I have a simple query Name and Date of Birth. I want to calculate the age in 
a query. I have tried different criteria from MS Access Tutorials. Can 
someone please help me with a correct criteria to calculate the contacts age 
as of today from the date of birth.

Thanks,

Deeds37

Deeds37,

Without seeing what you tried... can you tell me why the below didn't work 
for you?

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


-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Deeds37" <Deeds37@discussions.microsoft.com> wrote in message 
news:298D415B-4690-4CED-A461-D47F1E1AFC4D@microsoft.com...
I have a simple query Name and Date of Birth. I want to calculate the age in
a query. I have tried different criteria from MS Access Tutorials. Can
someone please help me with a correct criteria to calculate the contacts age
as of today from the date of birth.

Thanks,

Deeds37 

It depends how you want the age returned.  If you just want the age in years
then a simple expression, of which there are a 1001 variations, will do it, e.
g.

DateDiff("yyyy",[DoB],Date()) - IIf(DateSerial(Year(Date()), Month([DoB]),
Day([DoB]))>Date(),1,0)

If you want it in years and months then a function such as this will do it:

Public Function GetAge(varDob, Optional varDateAt)

    Dim intYears As Integer, intMonths As Integer

    ' return date at current date if no second argument
    ' passed into function
    If IsMissing(varDateAt) Or IsNull(varDateAt) Then
        varDateAt = VBA.Date
    End If
    
    If Not IsNull(varDob) Then
        ' get difference in years
        intYears = DateDiff("yyyy", varDob, varDateAt)
        ' get difference in months
        intMonths = DateDiff("m", varDob, _
            DateSerial(Year(varDob), Month(varDateAt), Day(varDateAt)))
        ' adjust result if date of birth falls later in year
        ' than date at which age to be calculated
        If varDateAt < DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
            intYears = intYears - 1
            intMonths = intMonths + 12
        End If
    
        GetAge = intYears & " year" & _
            IIf(intYears <> 1, "s", "") & ", " & _
            intMonths & " month" & _
            IIf(intMonths <> 1, "s", "")
    End If
    
End Function

Call it with:

GetAge([DoB])

or you can add a second argument to give you the age on a specific date
rather than the current date e.g. on 1 January this year:

GetAge([DoB],#2010-01-01#)

Ken Sheridan
Stafford, England

Deeds37 wrote:
>I have a simple query Name and Date of Birth. I want to calculate the age in 
>a query. I have tried different criteria from MS Access Tutorials. Can 
>someone please help me with a correct criteria to calculate the contacts age 
>as of today from the date of birth.
>
>Thanks,
>
>Deeds37

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1

Thanks Gina,
I put this formula in a text box in a form and it works.  What I am looking 
for is when I write a simple query - Name, Date of Birth........I want it to 
calculate the age. Do I put this formula in criteria under date of birth? 

"Gina Whipp" wrote:

> Deeds37,
> 
> Without seeing what you tried... can you tell me why the below didn't work 
> for you?
> 
> =DateDiff("yyyy", [YourBirthDateField], Now())+ Int( Format(Now(), "mmdd") < 
> Format( [YourBirthDateField], "mmdd") )
> 
> 
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
> 
> "I feel I have been denied critical, need to know, information!" - Tremors 
> II
> 
> http://www.regina-whipp.com/index_files/TipList.htm
> 
> "Deeds37" <Deeds37@discussions.microsoft.com> wrote in message 
> news:298D415B-4690-4CED-A461-D47F1E1AFC4D@microsoft.com...
> I have a simple query Name and Date of Birth. I want to calculate the age in
> a query. I have tried different criteria from MS Access Tutorials. Can
> someone please help me with a correct criteria to calculate the contacts age
> as of today from the date of birth.
> 
> Thanks,
> 
> Deeds37 
> 
> 
> .
> 

"Deeds37" <Deeds37@discussions.microsoft.com> a écrit dans le message de 
news:298D415B-4690-4CED-A461-D47F1E1AFC4D@microsoft.com...
>I have a simple query Name and Date of Birth. I want to calculate the age 
>in
> a query. I have tried different criteria from MS Access Tutorials. Can
> someone please help me with a correct criteria to calculate the contacts 
> age
> as of today from the date of birth.
>
> Thanks,
>
> Deeds37 

Deeds37,

Post the SQL of your query here...  it should work in a query.

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Deeds37" <Deeds37@discussions.microsoft.com> wrote in message 
news:03E0F135-B3E6-4A9C-96EC-075E336691E4@microsoft.com...
Thanks Gina,
I put this formula in a text box in a form and it works.  What I am looking
for is when I write a simple query - Name, Date of Birth........I want it to
calculate the age. Do I put this formula in criteria under date of birth?

"Gina Whipp" wrote:

> Deeds37,
>
> Without seeing what you tried... can you tell me why the below didn't work
> for you?
>
> =DateDiff("yyyy", [YourBirthDateField], Now())+ Int( Format(Now(), "mmdd") 
> <
> Format( [YourBirthDateField], "mmdd") )
>
>
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> http://www.regina-whipp.com/index_files/TipList.htm
>
> "Deeds37" <Deeds37@discussions.microsoft.com> wrote in message
> news:298D415B-4690-4CED-A461-D47F1E1AFC4D@microsoft.com...
> I have a simple query Name and Date of Birth. I want to calculate the age 
> in
> a query. I have tried different criteria from MS Access Tutorials. Can
> someone please help me with a correct criteria to calculate the contacts 
> age
> as of today from the date of birth.
>
> Thanks,
>
> Deeds37
>
>
> .
> 

On Wed, 24 Feb 2010 11:34:04 -0800, Deeds37
<Deeds37@discussions.microsoft.com> wrote:

>Thanks Gina,
>I put this formula in a text box in a form and it works.  What I am looking 
>for is when I write a simple query - Name, Date of Birth........I want it to 
>calculate the age. Do I put this formula in criteria under date of birth? 

No: put it in a vacant Field cell in the query grid. Putting it under the date
of birth will give you a "criterion" which will end up selecting no records at
all.
-- 

             John W. Vinson [MVP]