Bill Henderson
asked on
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
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
You should not even store the Age - it is a derived value and should be calculated any time it is needed.
ASKER
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
Would you mind kicking me some vbscript? =)
I have a Recordset named 'Users' and the field to pull is DateOfBirth
Thanks
Bill
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, so I have added this to a page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% bd=#1975-12-02# %>
<% ? datediff("yyyy",bd,Date()) -iif(Datep art("y",Da te())<Date part("y",b d),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(Datep art("y",Da te())<Date part("y",b d),1,0)
^
Any ideas?
Thanks again
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% bd=#1975-12-02# %>
<% ? datediff("yyyy",bd,Date())
%>
And I'm getting a compilation error:
Microsoft VBScript compilation error '800a0408'
Invalid character
/age_test.asp, line 3
? datediff("yyyy",bd,Date())
^
Any ideas?
Thanks again
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(Datep art("y",Da te())<Date part("y",b d),1,0)
varAge = datediff("yyyy",bd,Date())
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.
ASKER
Sorry I'm such a beginner with code. Here is my new code:
<% bd="1975-12-02" %>
<% varAge = datediff("yyyy",bd,Date()) -iif(Datep art("y",Da te())<Date part("y",b d),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
<% bd="1975-12-02" %>
<% varAge = datediff("yyyy",bd,Date())
%>
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
That is how I have my system set for the short date format. Your server is most probably different.
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)
ASKER
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.ActiveConne ction = MM_TotalBodyTracker_STRING
Recordset1_cmd.CommandText = "SELECT * FROM Users WHERE UserID = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters. Append Recordset1_cmd.CreateParam eter("para m1", 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("D ateOfBirth ").Value)
'Calculate the difference using DateDiff
AgeInYears = DateDiff("yyyy", Birthday, Now)
%>
Then in my page I have a
<p> You are <%Response.Write(AgeInYear s)%></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
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.ActiveConne
Recordset1_cmd.CommandText
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<%
'Define the variables
Dim Birthday, AgeInYears
'Get the users DOB
Birthday = (Recordset1.Fields.Item("D
'Calculate the difference using DateDiff
AgeInYears = DateDiff("yyyy", Birthday, Now)
%>
Then in my page I have a
<p> You are <%Response.Write(AgeInYear
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
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.