Calc date problem

[Gerard]

I have an inherited Db with a query that calcs a date via dateadd: -

DateAdd("yyyy",[Term],(DateAdd("d",[Adjustment],[EmployStartDate])))

This works fine but I can not seem to put a criteria against this calc
field and keep getting data type mismatch error.

I’ve tried: -

#31/12/2006#

Cdate(31/12/2006)

All help/suggestions welcome.

Cheers – Phil

 

[Rick Brandt]

I have an inherited Db with a query that calcs a date via dateadd: -

DateAdd("yyyy",[Term],(DateAdd("d",[Adjustment],[EmployStartDate])))

This works fine but I can not seem to put a criteria against this calc
field and keep getting data type mismatch error.

I’ve tried: -

#31/12/2006#

Cdate(31/12/2006)

All help/suggestions welcome.

Cheers – Phil

Date literals in Access queries need to be in either US formats (mm/dd/yy or
mm/dd/yyyy), or in ISO format (yyyy-mm-dd), or in a format where the month uses
characters (mmm-dd-yyyy). The DateSerial() should have worked for you though.

Are you sure that the DateAdd() expression you posted is the only thing in that
field? There is nothing that might be converting the type to a string (like
Format())?

 

[Gerard]

I have an inherited Db with a query that calcs a date via dateadd: -

DateAdd("yyyy",[Term],(DateAdd("d",[Adjustment],[EmployStartDate])))

This works fine but I can not seem to put a criteria against this calc
field and keep getting data type mismatch error.

I’ve tried: -

#31/12/2006#

Cdate(31/12/2006)

All help/suggestions welcome.

Cheers – Phil

Date literals in Access queries need to be in either US formats (mm/dd/yy or
mm/dd/yyyy), or in ISO format (yyyy-mm-dd), or in a format where the month uses
characters (mmm-dd-yyyy). The DateSerial() should have worked for you though.

Are you sure that the DateAdd() expression you posted is the only thing in that
field? There is nothing that might be converting the type to a string (like
Format())?

Rick - no the above is a direct copy of formula in query and yes thought
I might have to use US format as I'm in Australia so our date format is
different.

Dateserial has worked for me before on tricky date calcs??

 

[Rick Brandt]

Rick - no the above is a direct copy of formula in query and yes
thought I might have to use US format as I'm in Australia so our date
format is different.

Dateserial has worked for me before on tricky date calcs??

Well the help file does state that both DateAdd() and DateSerial() return a
Variant with sub-type of date so perhaps you do need to wrap the DateAdd()
in a CDate() function.

You might also be able to specify that expression as a DateTime type in the
parameters box to solve the problem.