MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Criteria for age calculation

• Follow

```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
as of today from the date of birth.

Thanks,

Deeds37
```
 0

```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
as of today from the date of birth.

Thanks,

Deeds37

```
 0

```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
>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

```
 0

```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
> as of today from the date of birth.
>
> Thanks,
>
> Deeds37
>
>
> .
>
```
 0

```"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
> age
> as of today from the date of birth.
>
> Thanks,
>
> Deeds37

```
 0

```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
> age
> as of today from the date of birth.
>
> Thanks,
>
> Deeds37
>
>
> .
>

```
 0

```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]
```
 0

6 Replies
627 Views

Similiar Articles:

7/21/2012 9:43:15 PM