Age calculation (1 Viewer)

[indesisiv]

I have a form with a Date of Birth on it. I also have a text box that needs to be the clients current age. I am trying to do something like Me.txtAge.Text = Date - Me.txtDob.Text
but the problem that I get with this is that I get a type mismatch because the second part of the calculation is coming out as a string.

So the question is this: Is there a way to convert the string date (looks something like "18/04/1977") into an actual date for the calculation. Or is there an easier way.

Thanks in advance for any help.

Steve

 

[Ally]

The way that I did it was in a query that feeds the form, make a new "field" to appear in the field list (but is not stored in the table), called

Age: ... .... (with your datediff etc here)

Then all you need to do is have a text box looking at Age in the Control Source.

 

[Oldsoftboss]

Check out datediff in vbhelp. not that clear but there are many topics in this forum on the subject. Just search for datediff

Good luck

Dave

 

[Ally]

Sorry - had misread your first post. Not realized you weren't sure of how to convert it. (I'll wake up shortly!) Do the same as before by putting Age into a query and the DateDiff function you need is:

Age: DateDiff("d",[DoB],Now())/365.254

(DoB is the fieldname - not sure what you've called yours, but you just need to replace it).

What this is doing is working out the number of days between the DoB and Now and then the "/365.254" is doing is dividing it by no of days in the year to give you a figure in years. There are a number of theories about the 365.254 and you may find others will post to say of other ways, so obviously play around and see what suits you.

 

[indesisiv]

Thank you

Thank you Ally. I have used
DateDiff("d", [DoB], Now()) / 365.254
and it seems to work fine now.

Didn't know that the DateDiff was there.

 

[Oldsoftboss]

Just found this while browsing for totally unrelated stuff at tek-tips.com - http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/181/fid/85

Hope it helps

Function Age (Birthdate as Date) as Integer
Age = DateDiff("yyyy", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
End Function

This takes advantage of the fact that True has a value of -1 and False has a value of 0... If this expression is True (the birthdate in the current year hasn't happened yet), you end up subtracting 1 day.

If you want to enter your own end date instead of using today's date, here is the modification:

Function Age(Birthdate As Date, Enddate as Date) As Integer
Age = DateDiff("yyyy", Birthdate, Enddate) + _
(Enddate < DateSerial (Year(Enddate), Month(Birthdate), Day(Birthdate)))
End Function

Dave