syntax error (comma) in expression

[Guest]

I am very new at this and can't seem to figure something out. I am using the
common formula for calculating ages. ( I added it as a calculated field in my
query and I have a DOB field so I thought it would work.)
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
But I am getting the message "syntax error (comma) in expression when I try
to run the query. Not sure why or how to resolve the problem. I have followed
a couple of threads after searching on the error but they haven't helped me
understand what to take out or what to replace the commas with, if anything.
I am using access 2002(XP)
Thanks

 

[John Vinson]

I am very new at this and can't seem to figure something out. I am using the
common formula for calculating ages. ( I added it as a calculated field in my
query and I have a DOB field so I thought it would work.)
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

You don't happen to have some other expression in the query that has a
misplaced comma? This looks OK. Another possibility is if you have a
field named Date (or Year or Month or some other reserved word) and
Access is getting confused.

Perhaps you could post the full SQL view of the query.

John W. Vinson[MVP]

 

[Guest]

That worked. Thank you! And I have seen a post that detailed the formula for
displaying months and days but all I need is the years, again thanks.

For age in years just use DateDiff("yyyy",[DOB],Date()) but maybe you want
to also display months and days.

Search post for age.

I am very new at this and can't seem to figure something out. I am using the
common formula for calculating ages. ( I added it as a calculated field in my
query and I have a DOB field so I thought it would work.)
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
But I am getting the message "syntax error (comma) in expression when I try
to run the query. Not sure why or how to resolve the problem. I have followed
a couple of threads after searching on the error but they haven't helped me
understand what to take out or what to replace the commas with, if anything.
I am using access 2002(XP)
Thanks

 

[Guest]

Thanks, The formula does work and you were right, access was getting
confused. I have an Entity table in the query twice(Entity, Entity_1) to
identify clients and employees and it got confused which one to pull the DOB
field from(it told me it was confused but I didn't know how to point it right
at first. Once I added the tablename into the expression correctly, it
worked. I didn't understand the error message about the comma that it gave me
after I incorrectly pointed to the Entity table(still don't) but at least
I've learned some things.
Thanks again.

The formula I originally used
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
works better than the shorter one
DateDiff("yyyy",[DOB],Date()) because it takes into account if a person
hasn't passed their birthday date yet this year when the query is run, it
correctly shows 15 years old instead of 16 years old (born june of 90 for
example, the shorter expression shows the person as 16 years old whereas the
original one I tried shows it correctly as 15-although without any totals of
months or days).

I am very new at this and can't seem to figure something out. I am using the
common formula for calculating ages. ( I added it as a calculated field in my
query and I have a DOB field so I thought it would work.)
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

You don't happen to have some other expression in the query that has a
misplaced comma? This looks OK. Another possibility is if you have a
field named Date (or Year or Month or some other reserved word) and
Access is getting confused.

Perhaps you could post the full SQL view of the query.

John W. Vinson[MVP]

 

[John Vinson]

For age in years just use DateDiff("yyyy",[DOB],Date()) but maybe you want
to also display months and days.

Well... try a DOB of #12/31/2001# and a Date() value of #1/1/2002#.
You'll find that this day-old (maybe minutes old) baby is shown as
being a year old. DateDiff counts year *boundaries*, not whole years.

John W. Vinson[MVP]