How do I calculate ages in Access?

[Guest]

It's me again. I'm not sure if I can do this or not, but can I have my
Consumer's ages automatically calculated on my forms? I have tried doing
what the website suggests and used different expressions, but none seem to
work. Am I doing something wrong?

 

[Guest]

I have read all the other posts, and as I stated, I have tried the things
they suggest, but it is not working. I have my form created, I put an
unbound control in it and put the formula that is suggested, but when I view
my form, it does not give me an age, it says, "#Name?" What am I doing wrong?

Am I doing something wrong? Well, you don't tell us SPECIFICALLY what you
have tried, so we can't answer that question.

The most common way is to add an unbound control to your form and put the
following formula in it...


=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))





Please note, this is asked and answered all the time. In the future, I'd
suggest you search for your answers before posting a new thread. The
easiest way I have found is to go to www.google.com, click the "groups"
options, and enter a search string starting with the following...

microsoft.public.access calculate age



--
Rick B

It's me again. I'm not sure if I can do this or not, but can I have my
Consumer's ages automatically calculated on my forms? I have tried doing
what the website suggests and used different expressions, but none seem to
work. Am I doing something wrong?

 

[Rick B]

What formula are you using? Post it here for us.

You DO have the birthdate of the person on your form, right? That field is
a DATE field, right? You are using that field name in your formula?



--
Rick B

I have read all the other posts, and as I stated, I have tried the things
they suggest, but it is not working. I have my form created, I put an
unbound control in it and put the formula that is suggested, but when I view
my form, it does not give me an age, it says, "#Name?" What am I doing wrong?

Am I doing something wrong? Well, you don't tell us SPECIFICALLY what you
have tried, so we can't answer that question.

The most common way is to add an unbound control to your form and put the
following formula in it...


=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))





Please note, this is asked and answered all the time. In the future, I'd
suggest you search for your answers before posting a new thread. The
easiest way I have found is to go to www.google.com, click the "groups"
options, and enter a search string starting with the following...

microsoft.public.access calculate age



--
Rick B

It's me again. I'm not sure if I can do this or not, but can I have my
Consumer's ages automatically calculated on my forms? I have tried doing
what the website suggests and used different expressions, but none seem to
work. Am I doing something wrong?

 

[Guest]

I am using the formula you gave me, with the field name ConsumerADOB:
=DateDiff("yyyy",[ConsumerADOB],Date())+(Format([ConsumerADOB],"mmdd")>Format(Date),"mmdd"))
The birthdate IS on the form. That field IS a date field. And I AM using
that field name in my formula.

I have gona to the google groups list like you suggested and they have the
same formulas and steps listed.

What formula are you using? Post it here for us.

You DO have the birthdate of the person on your form, right? That field is
a DATE field, right? You are using that field name in your formula?



--
Rick B

I have read all the other posts, and as I stated, I have tried the things
they suggest, but it is not working. I have my form created, I put an
unbound control in it and put the formula that is suggested, but when I view
my form, it does not give me an age, it says, "#Name?" What am I doing wrong?

Am I doing something wrong? Well, you don't tell us SPECIFICALLY what you
have tried, so we can't answer that question.

The most common way is to add an unbound control to your form and put the
following formula in it...


=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))





Please note, this is asked and answered all the time. In the future, I'd
suggest you search for your answers before posting a new thread. The
easiest way I have found is to go to www.google.com, click the "groups"
options, and enter a search string starting with the following...

microsoft.public.access calculate age



--
Rick B



It's me again. I'm not sure if I can do this or not, but can I have my
Consumer's ages automatically calculated on my forms? I have tried doing
what the website suggests and used different expressions, but none seem to
work. Am I doing something wrong?

 

[Rick B]

Your formula does not match what I posted.

look at the end. You have ...")>Format(Date),"...

It should be ...")>Format(Date(),"mm...




--
Rick B

I am using the formula you gave me, with the field name ConsumerADOB:
=DateDiff("yyyy",[ConsumerADOB],Date())+(Format([ConsumerADOB],"mmdd")>Forma
t(Date),"mmdd"))
The birthdate IS on the form. That field IS a date field. And I AM using
that field name in my formula.

I have gona to the google groups list like you suggested and they have the
same formulas and steps listed.

What formula are you using? Post it here for us.

You DO have the birthdate of the person on your form, right? That field is
a DATE field, right? You are using that field name in your formula?



--
Rick B

I have read all the other posts, and as I stated, I have tried the things
they suggest, but it is not working. I have my form created, I put an
unbound control in it and put the formula that is suggested, but when

I
view

my form, it does not give me an age, it says, "#Name?" What am I

doing
wrong?

:

Am I doing something wrong? Well, you don't tell us SPECIFICALLY

what
you

have tried, so we can't answer that question.

The most common way is to add an unbound control to your form and

put
the

following formula in it...

=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date

(),"mmdd"))





Please note, this is asked and answered all the time. In the

future,
I'd

suggest you search for your answers before posting a new thread. The
easiest way I have found is to go to www.google.com, click the "groups"
options, and enter a search string starting with the following...

microsoft.public.access calculate age



--
Rick B



It's me again. I'm not sure if I can do this or not, but can I

have
my

Consumer's ages automatically calculated on my forms? I have

tried
doing

what the website suggests and used different expressions, but none seem to
work. Am I doing something wrong?

 

[Guest]

Would you mind telling me how you fixed this because I have the same problem

I have a field called Birthdate and it is a date field (shortdate)

I have an unbound field and I placed the following formula in the Control
Source

=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format([Date](),"mmdd"))

I also get "#name?" as the result

--
Kellie

Nevermind, I did it.

I fixed the formula, but it still says "#name?" in my age block.

Your formula does not match what I posted.

look at the end. You have ...")>Format(Date),"...

It should be ...")>Format(Date(),"mm...




--
Rick B



I am using the formula you gave me, with the field name ConsumerADOB:

=DateDiff("yyyy",[ConsumerADOB],Date())+(Format([ConsumerADOB],"mmdd")>Forma
t(Date),"mmdd"))
The birthdate IS on the form. That field IS a date field. And I AM using
that field name in my formula.

I have gona to the google groups list like you suggested and they have the
same formulas and steps listed.


:

What formula are you using? Post it here for us.

You DO have the birthdate of the person on your form, right? That field
is
a DATE field, right? You are using that field name in your formula?



--
Rick B



I have read all the other posts, and as I stated, I have tried the
things
they suggest, but it is not working. I have my form created, I put an
unbound control in it and put the formula that is suggested, but when
I
view
my form, it does not give me an age, it says, "#Name?" What am I
doing
wrong?

:

Am I doing something wrong? Well, you don't tell us SPECIFICALLY
what
you
have tried, so we can't answer that question.

The most common way is to add an unbound control to your form and
put
the
following formula in it...




=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))





Please note, this is asked and answered all the time. In the
future,
I'd
suggest you search for your answers before posting a new thread.
The
easiest way I have found is to go to www.google.com, click the
"groups"
options, and enter a search string starting with the following...

microsoft.public.access calculate age



--
Rick B



message
It's me again. I'm not sure if I can do this or not, but can I
have
my
Consumer's ages automatically calculated on my forms? I have
tried
doing
what the website suggests and used different expressions, but none
seem to
work. Am I doing something wrong?

 

[Andy Smart]

Would you mind telling me how you fixed this because I have the same
problem

I have a field called Birthdate and it is a date field (shortdate)

I have an unbound field and I placed the following formula in the Control
Source

=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format([Date](),"mmdd"))

I also get "#name?" as the result

snipped
I gave up on trying to get the right answer out of datediff directly and use
this function

Function agsAgeAt(dtDOB, Optional dtAsAt As Date) As String
Dim intMnth As Integer
Dim intYr As Integer
If IsNull(dtDOB) Then
agsAgeAt = ""
Else
If dtAsAt = 0 Then dtAsAt = Now
intYr = Year(dtAsAt) - Year(dtDOB)
If DateAdd("yyyy", intYr, dtDOB) > dtAsAt Then intYr = intYr - 1
intMnth = DateDiff("m", DateAdd("yyyy", intYr, dtDOB), dtAsAt)
If DateAdd("m", intMnth, DateAdd("yyyy", intYr, dtDOB)) > dtAsAt Then
intMnth = intMnth - 1
If intYr > 0 Then agsAgeAt = intYr & "yrs "
agsAgeAt = agsAgeAt & intMnth & "mnths"
End If
End Function

I'm sure there are more elegant ways of doing it!

Andy

 

[Guest]

Thanks Andy

Yes I have seen this code but was unsure how/when to call this Function to
poulate the Age field after I have entered the Birthdate
--
Kellie

Would you mind telling me how you fixed this because I have the same
problem

I have a field called Birthdate and it is a date field (shortdate)

I have an unbound field and I placed the following formula in the Control
Source

=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format([Date](),"mmdd"))

I also get "#name?" as the result

snipped
I gave up on trying to get the right answer out of datediff directly and use
this function

Function agsAgeAt(dtDOB, Optional dtAsAt As Date) As String
Dim intMnth As Integer
Dim intYr As Integer
If IsNull(dtDOB) Then
agsAgeAt = ""
Else
If dtAsAt = 0 Then dtAsAt = Now
intYr = Year(dtAsAt) - Year(dtDOB)
If DateAdd("yyyy", intYr, dtDOB) > dtAsAt Then intYr = intYr - 1
intMnth = DateDiff("m", DateAdd("yyyy", intYr, dtDOB), dtAsAt)
If DateAdd("m", intMnth, DateAdd("yyyy", intYr, dtDOB)) > dtAsAt Then
intMnth = intMnth - 1
If intYr > 0 Then agsAgeAt = intYr & "yrs "
agsAgeAt = agsAgeAt & intMnth & "mnths"
End If
End Function

I'm sure there are more elegant ways of doing it!

Andy

 

[Andy Smart]

Thanks Andy

Yes I have seen this code but was unsure how/when to call this Function to
poulate the Age field after I have entered the Birthdate

snipped

Setting the Age field data - control source to =agsageat([Birthdate]) will
work with saved Birthdate data. For new or changed Birthdate values your
choices include clicking somewhere else on the form, setting up an 'Update'
command button, moving to the next record and back etc etc

 

[Guest]

As the age block's control source I have
=Age([ConsumerADOB]) & " yrs " & AgeMonths([ConsumerADOB]) & " mos"

And a mod:

Option Compare Database

'==========================================================
' General Declaration
'==========================================================
Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified date to today's date.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birth date).
'
' RETURN
' Age in years.
'
'*************************************************************
Function Age(varConsumerADOB As Variant) As Integer
Dim varAge As Variant

If IsNull(varConsumerADOB) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varConsumerADOB, Now)
If Date < DateSerial(Year(Now), Month(varConsumerADOB), _
Day(varConsumerADOB)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

'*************************************************************
' FUNCTION NAME: AgeMonths()
'
' PURPOSE:
' Compliments the Age() function by calculating the number of months
' that have expired since the last month supplied by the specified date.
' If the specified date is a birthday, the function returns the number of
' months since the last birthday.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birthday).
'
' RETURN
' Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal StartDate As String) As Integer

Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

I hope that helps.

Thanks Andy

Yes I have seen this code but was unsure how/when to call this Function to
poulate the Age field after I have entered the Birthdate

snipped

Setting the Age field data - control source to =agsageat([Birthdate]) will
work with saved Birthdate data. For new or changed Birthdate values your
choices include clicking somewhere else on the form, setting up an 'Update'
command button, moving to the next record and back etc etc