DateAdd?

[Darrell]

I am attempting to use the DateAdd function with MS Access 2003 inside a
query.

The following does not work. Is it possible to use the dateadd function in
the WHERE clause?

SELECT CustomerName FROM Insurance
WHERE
(#12/29/03# between DateAdd('d',-1,[Insurance].[Expiration Date]) and
DateAdd('ww',1,[Insurance].[Expiration Date]))

All help would be greately appreciated.

TIA
Darrell

 

[John Spencer (MVP)]

Don't have 2003 available, but tested similar expression against 97 and it works
there. So I would expect you have some other problem.

By the way, what do you mean "does not work"? Do you get an error message, the
wrong records, no records?

You might try rewriting your where clause so that you are checking if the
expiration date is within a range. It will probably be faster, especially if
Expiration date is an indexed field.

WHERE Insurance.[Expiration Date]
between #12/30/03# and #1/5/04#

or
WHERE Insurance.[Expiration Date]
between Date() and DateAdd("ww",1,Date())

 

[Guest]

You may have problems if your dates are stored with hours
and minutes (by using Now() function).
It is easier to say
WHERE #12/29/03# BETWEEN [Expiration Date]-1 AND
[Expiration Date]+1
than using DateAdd function.
If your dates include hours and minutes, you may need to
say [Expiration Date]+2 instead of [Expiration Date]+1