changing users age based on date of birth

Hi,

I have a database that contains user info, including DateOfBirth and CurrentAge. There is other functionality on the site that requires the age to be updated accurately each year.

I THINK what I need is a server side script that scans the user DB once a day and locates any DateOfBirth values that match today's date and then adds +1 to the value in the CurrentAge field for that user.

Does that sound right? Is there a more elegant approach? Where would I find such a script? I have complete control over our server, but I don't want to have anything too processor intensive running everyday.

Any ideas?

Thank you

Bill
LVL 1
billium99Asked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
For example:

bd=#1975-12-02#
? datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)
 31
bd=#1975-12-01#
? datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)
 32

This will always give a correct value for any birthday bd.
0
 
GRayLCommented:
You should not even store the Age - it is a derived value and should be calculated any time it is needed.
0
 
billium99Author Commented:
Oh! So just calculate Date of Birth live? OK - that makes sense, but I'm not sure how I would do that.

Would you mind kicking me some vbscript? =)

I have a Recordset named 'Users' and the field to pull is DateOfBirth

Thanks

Bill
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this link for sample function on how to calculate age

http://support.microsoft.com/?kbid=210522
0
 
billium99Author Commented:
OK, so I have added this to a page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% bd=#1975-12-02# %>
<% ? datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)
%>

And I'm getting a compilation error:

Microsoft VBScript compilation error '800a0408'

Invalid character

/age_test.asp, line 3

? datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)
^

Any ideas?

Thanks again
0
 
GRayLCommented:
I was running this in the Immediate Pane of Access VB Editor, hence the question mark.  You should use your variable name for Age

varAge = datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)

0
 
GRayLCommented:
Also, replace bd with what ever varialbe you use to represent the birthdate in your code.  I used bd and predefined it.  Sorry I am not up to speed with on ADP's and the like.
0
 
billium99Author Commented:
Sorry I'm such a beginner with code. Here is my new code:

<% bd="1975-12-02" %>
<% varAge = datediff("yyyy",bd,Date())-iif(Datepart("y",Date())<Datepart("y",bd),1,0)
%>

If i use either quotes or the ##, I get type mismatch on iif

I'm familiar with associating bd with my database value, but I can't even get it to work with an actual date. Could my server be set up to use a different date format here?

Or do you know the nature of the type mismatch?

Thanks
0
 
GRayLCommented:
That is how I have my system set for the short date format.  Your server is most probably different.  
0
 
GRayLCommented:
I think you need the pound signs (#) to delimit the date along with the correct presentation of the date for your server.  If it is USA, try #12/01/75# (1 Dec 1975)
0
 
billium99Author Commented:
OK, so I did end up using datediff - here is the syntax I finally got to work:
First a recordset to pull my DOB record - this will need filtering eventually but for now there is only one record in there:

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request("MM_EmptyValue") <> "") Then
  Recordset1__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_TotalBodyTracker_STRING
Recordset1_cmd.CommandText = "SELECT * FROM Users WHERE UserID = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 5, 1, -1, Recordset1__MMColParam) ' adDouble

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<%

'Define the variables
Dim Birthday, AgeInYears
'Get the users DOB
Birthday = (Recordset1.Fields.Item("DateOfBirth").Value)
'Calculate the difference using DateDiff
AgeInYears = DateDiff("yyyy", Birthday, Now)

%>

Then in my page I have a

<p> You are <%Response.Write(AgeInYears)%></p>

And that did it. So Datediff was right with different syntax, and while Capricorn's link did not lead to a direct solution, it got me off searching syntax, which led to the right answer.

Thanks for the help!

Bill
0
 
GRayLCommented:
You do realize that that your formula for age will be in error by one year until you get past the birthdate in the year of Date().  That is why I had my IIF() statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.