DateDiff function-HElP!

[Toby1kenobe]

I'm trying to find a students' age using this:

Private Sub DOB_AfterUpdate()

Age = DateDiff("yyyy", DOB, Now)

End Sub

if in the DOB (date of birth) field on the form i put 1/1/90
i get the value 103. I'm not great at maths but i know this isnt even close!!

Can anybody help, otherwise I'll be 103 before i sort it!

Anyhelp as always greatly appreciated
Toby

 

[KenReay]

hi

would you try the following variations and post back the results

Age = DateDiff("yyyy", DOB, Date)

Age = DateDiff("yyyy", CDate(DOB), Date)

set dob to 01/01/1990

and repeat above





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[JeremyNYC]

Well I just did the following in the immediate window:
?datediff("yyyy",#1/1/1990#,now)
12
and as you can see, if gives a proper result (though I'm not sure why you'd want an integer in four-digit format).

Is the calendar on your computer set to something funky?

Is DOB a fully qualified reference in the context in which you're using this? What about me!dob?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[Toby1kenobe]

Thanks for the replies, i feel just a bit stupid. After posting this i realised that I'd set the DOB field to a date field. Turning this to integer solved the problem.

Sorry to waste your time!

Thanks again
Toby

 

[JeremyNYC]

Actually, this is the WRONG solution. If you look at the example I posted:
?datediff("yyyy",#1/1/1990#,now)
12

You can see that it works with a two dates, as it says in the help file. Why it works for you I'm not sure, but I hope you'll post back with exactly what you're doing. DOB should clearly be a date field and NOT an integer field.

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[Toby1kenobe]

Dear Jeremy

Yes, DOB is a date field, it's the Age field that i had incorrectly set as date not integer. Sorry for causing confusion. If i keep this up somebody will blacklist my threads!

Toby

 

[JeremyNYC]

Ah, that makes sense. No, you're not getting anywhere near being blacklisted (not by me, anyway).

One thing to think about, though, is that you probably don't want to store the Age. This is something that you will always be able to calculate, and storing calculated values is pretty well frowned upon.

There's an article on my web page, written by Paul Litwin, that explains this rule, and the others that go into building proper databases. It's in the developers' section.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[MichaelRed]

Hmmmmmmmmmm,

While you appear to at least be getting 'reasonable' results, the posted soloution will (stastically) be wrong 50% of the time.

'DateDiff' counts the BOUNDARIES of the interval, so -AS WELL DOCUMENTED in help, you can get strange answers, like:

? DateDiff("yyyy", #12/31/01#, #1/1/02#)
 1

Which -while clearly 'STRANGE'- is exactly what the documentation says you should get.

There are NUMEROUS routines posted in these fora to determint the 'AGE' from DOB.

One such thread which includes MY contribution to the discussion is: thread181-196407


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[kramerica]

I know that Many posts have already been made.

Here is a routine that I use to calculate age based on their date of birth.

First I check their DOB to make sure that it is not null.
I would not advise using a DATEDIFF function for calculating AGE.

=IIf(IsNull([dob]),"",Year(Now())-(Year([dob])-(DateSerial(Year(Now()),Month([dob]),Day([dob]))>Now())))

Kramerica

 

[rhicks]

DateDiff(&quot;yyyy&quot;, DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))

RDH Ricky Hicks
Birmingham, Alabama