434,984 Members | 2,794 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

# Age from Date of Birth

 P: 83 I would like Age to automatically be calculated in years from the date of birth. I have a date of birth variable and an age variable. Upon update of the DOB, I would like the age to be calculated. If someones birthday is 11/9/1990, then as of today, the age should read 16, not 17 because they do not turn 17 until november 9th. Apr 3 '07 #1
8 Replies

 Expert 100+ P: 1,356 DateDiff Function Returns a Variant (Long) specifying the number of time intervals between two specified dates. Apr 3 '07 #2

 Expert 2.5K+ P: 3,532 Having been birthed on 11/09; well, we won't get into the year! Let's just say I've got shoes older then 1990 that aren't even broken in yet! I had to figure this out! Expand|Select|Wrap|Line Numbers  Age = (datediff("m","11/9/1990",now) - datediff("m ","11/9/1990",now)mod 12)/12 will yield 16, not 17! Good Luck! Apr 3 '07 #3

 P: 83 but...If someones birthday is after the current date, it will report the age to be 1 year higher than it is DateDiff Function Returns a Variant (Long) specifying the number of time intervals between two specified dates. Apr 3 '07 #4

 P: 83 I get "the value you entered isn't valid for the field" Apr 3 '07 #5

 P: 83 [code] Private Sub Date_of_Birth_AfterUpdate() Me.Age = DateDiff(m, Me.Date_of_Birth, Now) - (DateDiff(m, Me.Date_of_Birth, Now) Mod 12) End Sub [\code] yields invalid procedure Apr 3 '07 #6

 Expert 100+ P: 1,356 You were missing some quotes around your months. "m" Here is another way... Expand|Select|Wrap|Line Numbers DateDiff("yyyy", #11/12/1990#, Date) - IIf(Date < DateSerial(Year(Date), Month(#11/12/1990#), Day(#11/12/1990#)), 1, 0) Apr 3 '07 #7

 Expert 2.5K+ P: 3,532 As Denburt pointed out, you omitted the quatation marks around the month qualifier ( should be "m" not just m) but you also left off part of the formula! You have to be careful, when copying from the code inset on the forum, to make sure you get all the code that may be beyond the visible part! Your code should be: Private Sub Date_of_Birth_AfterUpdate() Me.Age = (DateDiff("m", Me.Date_of_Birth,Now) - DateDiff("m", Me.Date_of_Birth,Now) Mod 12)/12 End Sub Apr 4 '07 #8

 P: 36 Here's another way, without using DateDiff, supposing you're using some form of VB: Private Sub [Your Control] On Update vblAge = Format(Now - CDate(vblDOB), "yyyy") End Sub If you're using Access, it will be Format\$(...) Dunno how accurate it will be though. Apr 4 '07 #9