datediff question

  • Thread starter Thread starter Rob S
  • Start date Start date
R

Rob S

Hi,

I'm designing a report on which I need to calculate someones age based
on their birthdate. I don't know how to program in VB, so I was
borrowing code off of examples I found in this group, but everything I
try gives me a syntax error.

I have a field called TWINBDAT, which is a date/time field with the
short date format. I want to compare that to the current date to
calculate an age. I've tried a few of the different examples that
format answers for month and year and such, but get the same error for
everything I try. In its simplest form, the expression I am using is

DateDiff("yyyy", [TWINBDAT], #1/26/2005#())

In this example I just entered todays date since I really don't know
how to tell access to use the current days date. I tried 'now' but
since I always get an error message anyway, don't know of that works or
not, and figured I'd eliminate as many questionable things as I could.
The exact error message i get is "Syntax error (comma) in query
expression [DateDiff("yyyy", [TWINBDAT], #1/26/2005#())]

On the report, I inserted a bound object frame and am using the
expression in the data control source.

Any help on why this won't work would be greatly appreciated. I'm not
an Access expert by any stretch, but got shouldered with this task
because I know a bit more about databases in general than anyone else I
work with... Thanks for your help.

Rob S.
 
The first problem is the extra set of parentheses you've included: it's only
#1/26/2005#, not #1/26/2005#().

However, note that the DateDiff function tends to be a little too literal
when calculating differences. For example, DateDiff("yyyy", #12/31/2004#,
#1/26/2005#) is going to tell you that the age of that less-than-one-month
old is 1 year!

To combat this (and to provide the current day's date, rather than hard
coding it), use:

DateDiff("yyyy", [TWINBDAT], Date()) - _
IIf(Format(Date(), "mmdd") < Format([TWINBDAT], "mmdd"), 1, 0)
 
Thanks for the fast reply Doug! However, I am still getting the same
syntax error after copying and pasting your code into my control
source. Could there be something else I am doing wrong? Most of the
time when I'm having problems with Access its because I am overlooking
something completely obvious =(


Thanks again,

Rob S
 
Just to ensure I understand, you're trying to use the DateDiff expression as
the control source for a field? Exactly what are you typing in the Control
Source field? Are you remembering to include an equal sign at the beginning?
 
Yes, I am using the datediff expression as the control source for a
bound object frame I put on a report. I didn't include an equal sign
at the beginning, but after doing so I still get a syntax error. This
time it just says the expression contains invalid syntax. I 'typed'
in:

=DateDiff("yyyy", [TWINBDAT], Date()) - _IIf(Format(Date(), "mmdd") <
Format([TWINBDAT], "mmdd"), 1, 0)

So basically I just copied and pasted what you wrote into the control
source. Taking out the underscore before the IIf statement eliminates
the syntax error, but no age is reported. The TWINBDAT field is a
date/time field in the record source for the report. Is there any more
information you need from me?

Thanks again.

Rob S.
 
Yes, you definitely don't want the underscore, but other than that, it looks
okay.

Afraid I can't think of anything, other than possibly creating a function
that computes age, and then use that function as the control source.
 
Back
Top