Formula in Query

[Ady]

Hi All

I have a query that works out the age of a person from their birth date. The
formula for this is:

AgeInYears: DateDiff("m",[Date of Birth],Now())/12

This works fine.

I then have another field in the query that display a message if the age
worked out in this query is less than 18:

AGE: IIf([AgeInYears]<"18","Possible Age Restriction")

This works, but only under certain conditions. If a persons age is in
single figures the message will not display. I have one record where the age
is 8.33 and the formula does not work.

Any assistance offered would be most appreciated. Many thanks in advance.

 

[Wayne-I-M]

Here is a better one for you

NewDateOfBirth:
IIf(DateDiff("yyyy",[TableName]![DateofBirth],Now())-IIf(Format([TableName]![DateofBirth],"mmdd")>Format(Now(),"mmdd"),1,0)>18,"Adult","Possible
Age Restriction - Child age " &
Int(DateDiff("m",[TableName]![DateofBirth],Now())/12) & " years " &
IIf(DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12=1,"and " &
DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12 & "
month",IIf(DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12>1,"and " &
DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12 & " months")))

Of course change [TableName]![DateofBirth] to what they really are


You can change this to

NewDateOfBirth:
IIf(DateDiff("yyyy",[TableName]![DateofBirth],Now())-IIf(Format([TableName]![DateofBirth],"mmdd")>Format(Now(),"mmdd"),1,0)<18,"Possible
Age Restriction - Child age " &
Int(DateDiff("m",[TableName]![DateofBirth],Now())/12) & " years " &
IIf(DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12=1,"and " &
DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12 & "
month",IIf(DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12>1,"and " &
DateDiff("m",[TableName]![DateofBirth],Now()) Mod 12 & " months")))

which will cut out the word "adult" if they are over 18.

Good luck

 

[Klatuu]

For starters, you formula is not accurate. Depending on a person's
birthday, it can be 1 year off.
For example, if a person's birthday were 10/24/1943, you formula shows the
age as 65, but the actually, the person is 64 as he has not yet reached his
65th birthday.

But the reason your logic is not working is you are comparing using string
values rather than numeric values.

AGE: IIf([AgeInYears]<"18","Possible Age Restriction")

Shouldbe be
AGE: IIf([AgeInYears]< 18, "Possible Age Restriction")

If you want a more accurate function to calculate an age, this may be
helpful:

Public Function CalcAge(Bdate As Variant, Optional DateToday As Variant) As
Integer
Dim dtmToDate As Date

' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
' Returns 0 if Bdate is Null
' Uses current date if DateToday is missing or Null

If IsNull(Bdate) Then
CalcAge = 0
Else
If IsMissing(DateToday) Then
dtmToDate = Date
Else
dtmToDate = Nz(DateToday, Date)
End If
If Month(dtmToDate) < Month(Bdate) Or (Month(dtmToDate) = _
Month(Bdate) And Day(dtmToDate) < Day(Bdate)) Then
CalcAge = Year(dtmToDate) - Year(Bdate) - 1
Else
CalcAge = Year(dtmToDate) - Year(Bdate)
End If
End If
End Function

You can put the function in a public module and call it like this:

AgeInYears: CalcAge([Date of Birth])

 

[John Spencer]

Try comparing to a NUMBER and not a STRING.

IIF([AgeInYears]<18,"Possible Age Restriction",Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve Schapel]

Adrian,

By putting the ""s around the 18, you are defining it as text, and it
will therefore be sorted as text. Change it to:
AGE: IIf([AgeInYears]<18,"Possible Age Restriction")

Having said that, I am surprised you find the AgeInYears expression to
be satisfactory, as it will often be inaccurate, depending on which day
of the month the Date of Birth is.

 

[Ady]

Hi All

Many thanks indeed for all of the person the provided an answer.

What I found most helpful was Steve's comments regarding placing "" around a
number, Access then thinks it is text. I have removed the "" and it works
fine.

It is accurate enough for our small group of special needs persons.

Thank you again to all of you.

Regards

Adrian

Adrian,

By putting the ""s around the 18, you are defining it as text, and it will
therefore be sorted as text. Change it to:
AGE: IIf([AgeInYears]<18,"Possible Age Restriction")

Having said that, I am surprised you find the AgeInYears expression to be
satisfactory, as it will often be inaccurate, depending on which day of
the month the Date of Birth is.

--
Steve Schapel, Microsoft Access MVP

Hi All

I have a query that works out the age of a person from their birth date.
The formula for this is:

AgeInYears: DateDiff("m",[Date of Birth],Now())/12

This works fine.

I then have another field in the query that display a message if the age
worked out in this query is less than 18:

AGE: IIf([AgeInYears]<"18","Possible Age Restriction")

This works, but only under certain conditions. If a persons age is in
single figures the message will not display. I have one record where the
age is 8.33 and the formula does not work.

Any assistance offered would be most appreciated. Many thanks in advance.