Date Trouble (1 Viewer)

[Dazza666]

Hi,

i'm having problems getting a simple date calculation to work,

all i need is to subtract a DateOfBirth field from NOW()

and produce an age e.g. 08/01/1988 would produce 20

I'm doing this in the Exit event of the DateOfBirth field which populates the Age Field. The Age field is a number whilst the DateOfBirth is an age.


i've been messing with formatdatetime(), Dateserial(), datediff() FormatDateTime().

I can get it to calculate but it isn't accurate to the day only the year,

DateDiff is kicking up 'invalid procedure call'

I really can't work this out


thanks

 

[namliam]

A quick search will help you...

Calculate A Person's Age

 

[DCrake]

Simple Software Solutions

There are several appoaches of calculating ages, my logic is as follows

Public Function AgeToday(AnyDate As Date) As Integer

     AgeToday = Int(DateDiff("d",AnyDate,Date())/365.25)

End Function

I use the 365.26 division to contend with leap years.

Here is an example provided by raskew

Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days

Dim inthold As Integer
Dim dayHold As Integer

   inthold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), Day(StartDate)))

   If Day(EndDate) < Day(StartDate) Then
      dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(StartDate)
   End If
   
   fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
             & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
             & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")

End Function

 

[Mike375]

This will give you age in years

DateDiff("yyyy",[CLDOB],Now())+Int(Format(Now(),"mmdd")<Format([CLDOB],"mmdd"))

Replace CLDOB with your field name.

In a query you make it as a new field on the field row in design view. Add : to the new field name as

NewFieldNameateDiff("yyyy",[CL DOB],Now())+Int(Format(Now(),"mmdd")<Format([CL DOB],"mmdd"))

In an unbound textbox just enter =DateDiff("yyyy",[CL DOB],Now())+Int(Format(Now(),"mmdd")<Format([CL DOB],"mmdd"))

 

[Dazza666]

That Nailed it,

thanks to both of you but I used Dcrakes solution as i didn't understand the other one lol