Calculating Birthdates


G

Guest

I am having problems. I am trying to get the birthdates to calculate and show
up in a form. I have been use this

Age:
Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

In my query and I get an error of #Name? in my form where the birthday is to
calculate If some one could help me out.

Please advise.
 
Ad

Advertisements

C

Cilla

SarahLyn said:
I am having problems. I am trying to get the birthdates to calculate and show
up in a form. I have been use this

Age:
Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

In my query and I get an error of #Name? in my form where the birthday is to
calculate If some one could help me out.

Please advise.
The code is good except you do not have brackets around the DOB field.
Try an unbound field in the form with the same calc below as the
control source for the unbound field..

=Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))

should work fine

If you are pulling it from a query make sure you have no format on the
field in the form.
 
S

Steve Schapel

It is difficult to see what is the purpose of the Abs() function in this
expression. I don't think it's right. Try this...
Age:
DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd"))
 
S

Steve Schapel

It is difficult to see what is the purpose of the Abs() function in this
expression. I don't think it's right. Try this...
Age:
DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd"))
 
G

Guest

I trying to determine the age of a person and I was using this command in the
default value:

DateDiff("yyyy",[birth_date],Date())

and when i try to save the table it says

"The database engine does not recognize either the field 'birth_date' in a
validation expression, or the default value in the table 'general'

I believe it is the latter situation, but not sure how to fix it

Thanks
 
S

strive4peace

Hi Berek ,

Since Age is a calculation (and will change), it is something that
should be shown on a form or report rather than stored. You can't use
equations like that in the DefaultValue property of a table, but you can
set a DefaultValue like this for a form control ... but, in this case,
you wouldn't do that because the equation should be preceeded by an
equal sign (=) and used as the ControlSource of a calculated control

You should not put data directly into tables anyway.

Also, the equation you are using won't work properly if the person was
born in December and the current month is July because Access will just
take the difference of the years.

This was posted by Ricky Hicks:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the current age .. use:

DateDiff("yyyy", [DOBField], Date()) +
(Date() < DateSerial(Year(Date()), Month([DOBField]), Day([DOBField])))

"DOBField" should be the name of the field that contains the Date of
Birth value ...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
 
Ad

Advertisements

G

Guest

Thanks a lot, that was very useful and makes much more sense. There is no
need to store that info.... duh

Thanks again

Berek



strive4peace said:
Hi Berek ,

Since Age is a calculation (and will change), it is something that
should be shown on a form or report rather than stored. You can't use
equations like that in the DefaultValue property of a table, but you can
set a DefaultValue like this for a form control ... but, in this case,
you wouldn't do that because the equation should be preceeded by an
equal sign (=) and used as the ControlSource of a calculated control

You should not put data directly into tables anyway.

Also, the equation you are using won't work properly if the person was
born in December and the current month is July because Access will just
take the difference of the years.

This was posted by Ricky Hicks:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the current age .. use:

DateDiff("yyyy", [DOBField], Date()) +
(Date() < DateSerial(Year(Date()), Month([DOBField]), Day([DOBField])))

"DOBField" should be the name of the field that contains the Date of
Birth value ...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access


I trying to determine the age of a person and I was using this command in the
default value:

DateDiff("yyyy",[birth_date],Date())

and when i try to save the table it says

"The database engine does not recognize either the field 'birth_date' in a
validation expression, or the default value in the table 'general'

I believe it is the latter situation, but not sure how to fix it

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top