Calculate the difference between two dates

[helpless]

Access 2003 - I am trying to calculate the difference between a specific date 5/1/2007 (not in a data field) and other dates that ARE in a field called Birth Date. I am trying to have it fill into an Age field (in years only) by comparing the date above to the Birth Date field. I have tried this and it doesn't work:

=DateDiff("yyyy", [Birthday1], [Birthday2]) Displays the variance in years between the values of the Birthday1 and Birthday2 fields. (from Access help screen).

I have tried it several ways:

1) =DateDiff("yyyy", [5/1/2007], [Birth Date])

2) =DateDiff("yyyy", 5/1/2007, [Birth Date]) because 5/1/2007 is not a field name

3) setting up a field with 5/1/2007 in every record and doing #1

Nothing is working - getting errors - please help

[nico5038]

Check: http://www.mvps.org/access/datetime/date0001.htm
Many fine other samples there too.

Nic;o)

[helpless]

Hi,

I am trying diligently and getting this error:

Invalid Syntax error - cannot use multiple columns in a column-level CHECK constraint.

this is what I did: in the Validation Rule for Field name Age

=DateDiff("yyyy",(5012007),[Birth Date])

5/1/2007 is just the date I am comparing the birth dates to. It is nowhere else in the db.....can you help?

[nico5038]

Try:

=DateDiff("yyyy",#5/1/007#,[Birth Date])

and make sure the field with this statement isn't named [Birth Date] :-)

Nic;o)

[helpless]

Hi, see I really am helpless!!

I put this validation rule into the Age field:

=DateDiff("yyyy",#5/1/2007#,[Birth Date])

and I am still getting the same error. It looks like it thinks 5/1/2007 is a column/field, but it is not.

thanks anyway!

[nico5038]

Hmm, perhaps your separator is ; instead of a ,
Try:
=DateDiff("yyyy";#5/1/2007#;[Birth Date])

Nic;o)

[helpless]

Hi,

Thanks, I tried that, but then it gives me an invalid syntax error.

that's ok, I think I'll keep putting in the dates manually!

thanks

[MMcCarthy]

Hi,

Thanks, I tried that, but then it gives me an invalid syntax error.

that's ok, I think I'll keep putting in the dates manually!

thanks

This is not as straight forward as it first seemed

=DateDiff("yyyy", #5/1/2007#, [Birth Date])

The probem is that as of 1st January it will count the year so age won't be calculated correctly.

You need to adapt it as follows:

=DateDiff("yyyy", Format(#05/01/2007#,"Short Date"), IIf(Format([Birth Date], "mmdd") >= Format(#05/01/2007#,"mmdd"),[Birth Date],[Birth Date] + 1))

BTW, where exactly are you putting this calulation?

Mary

[NeoPa]

Mary is right, but the more fundamental problem of why the 'Validation Rule' actually throws up an error when you enter

=DateDiff("yyyy",#5/1/007#,[Birth Date])

is very confusing.
I have

=DateDiff("yyyy",#01/05/2006#,[EndJob])

working fine in my test db.