2. ASPRunnerPro General questions
  3. Age from Date of Birth
This topic is locked

Age from Date of Birth

 		1/24/2007 11:26:31
ASPRunnerPro General questions
G
 grinningdog author

I've got two fields. In one the user gives a date of birth, in the other I'd like to calculate their age as of 31/12/2007
What's the best way to achieve this? I'm guessing an AfterAdd event but the code below doesn't work
[codebox]Sub AfterAdd()

'** Insert a record into another table ****

strSQLInsert = "insert into TblMyTable (Age) values (31/12/2007 - DOB)"

dbConnection.Execute strSQLInsert
End Sub[/codebox]
I've got a similar problem with another field. I want ot calculate a category based on age and gender but I'm hoping the answer to the first will fit the second <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=4459&image=1&table=forumtopics' class='bbc_emoticon' alt=':rolleyes:' /> Or at least point me in the right direction!
Regards

Bob
J
 		Jane 1/25/2007

Hi,
use Before record added event for this purpose.

Here is a sample code:

Function BeforeAdd(dict)

str = "SELECT DateDiff('yyyy','" & dict("BirthDate") & "', Now()) AS Age FROM TableName"

Set rsTemp = server. CreateObject ( "ADODB.Recordset" )

rsTemp. open str , dbConnection

dict("Age") = rsTemp("Age")

BeforeAdd = True
End Function



where TableName is your actual table name, Age and Birthdate are your actual field names.
G
 		grinningdog author 1/25/2007

Jane
Thank you. That works perfectly on the Add page but when I try to copy it to the edit page it doesn't work. What am I doing wrong?
Bob
J
 		Jane 1/25/2007

Bob,
here is the correct code for the BeforeEdit event:

Function BeforeEdit(dict, where)

str = "SELECT DateDiff('yyyy','" & dict("BirthDate") & "', Now()) AS Age FROM TableName"

Set rsTemp = server. CreateObject ( "ADODB.Recordset" )

rsTemp. open str , dbConnection

dict("Age") = rsTemp("Age")

BeforeEdit = True
End Function
G
 		grinningdog author 1/25/2007

HaHa! I was trying to be too clever - always a mistake. I tried it using BeforeUpdate!
Thans very much. I'll try it now
E
 		ebolisa 1/25/2007

Hi Jane,

How about a pop up window (or a msg box) at login which checks DOB column vs. Now() in tblAgents and displays a message: Hi, today is "Bob's" Bday.

Just a thought to put a smile on someone's face... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=14890&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />

Rds,

Emilio
J
 		Jane 1/26/2007

Hi,
here is a sample code:

Sub AfterSuccessfulLogin()

str = "select DOB from TableName where UserName='" & Session("UserID") & "' and DOB=format(now,'Short Date')"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
if not rsTemp.eof then

Response.write "Hi, today is Bob's Bday."

end if
End Sub
E
 		ebolisa 1/28/2007

Hi Jane,

My project is connected to MSSQL so I changed the code a bit but still doesn't work.

What could it be?

TIA

Emilio
Sub AfterSuccessfulLogin()

str = "select DOB, AgentName from dbo.Agents where DOB=getdate()"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

if not rsTemp.eof then

Response.write "Hi, today is" & AgentName & "'s Bday."

end if

End Sub
J
 		Jane 1/29/2007

Here is the correct code:

Sub AfterSuccessfulLogin()

str = "select DOB, AgentName from dbo.Agents where DOB=getdate()"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

if not rsTemp.eof then

Response.write "Hi, today is " &rsTemp("AgentName") & "'s Bday."

end if
E
 		ebolisa 1/29/2007

Hi Jane,

I think the below code worked but didn't halt with the message or didn't display a msg box. Login jumps to the menu page - perhaps should I insert a 5 - 10s delay before the menu page is displayed so the user can see the message?:
str = "select DOB, AgentName from dbo.Agents WHERE (DATEPART(m, DOB) = DATEPART(m, GETDATE())) AND (DATEPART(d, DOB) = DATEPART(d, GETDATE()))"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

if not rsTemp.eof then

Response.write "Hi, today is " &rsTemp("AgentName") & "'s Bday."

end if