Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

changing users age based on date of birth

Posted on 2007-12-01
12
Medium Priority
?
335 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:billium99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 20388619
You should not even store the Age - it is a derived value and should be calculated any time it is needed.
0
 
LVL 1

Author Comment

by:billium99
ID: 20388657
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 600 total points
ID: 20388682
see this link for sample function on how to calculate age

http://support.microsoft.com/?kbid=210522
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 44

Accepted Solution

by:
GRayL earned 1400 total points
ID: 20388685
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
 
LVL 1

Author Comment

by:billium99
ID: 20388720
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20388741
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20388750
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
 
LVL 1

Author Comment

by:billium99
ID: 20388774
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20388807
That is how I have my system set for the short date format.  Your server is most probably different.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20388856
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
 
LVL 1

Author Comment

by:billium99
ID: 20389601
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20390031
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to dynamically set the form action using jQuery.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question