Help - MS Access query

[pmaths]

Hi,

I need to create a query that will report all records that occur within 5 days of the current date.

I've tried the following syntax with no real joy:

>now()-"5"
Returns all records greater than the current date - Seems to ignore the "-5".

>now()+"5"
Same as above.

Between Date() And Date()+"6"
I've had to enter "6" to include the fifth day after today - unfortunatly this syntax also returns dates where it is just the day date that is within 5 days. i.e. as well as returning 17/02/07 it would return records that are dated 17/03/07 - 18/04/07 - 16/05/07 etc...

Between Date() And (Date()+Date(6))
Same as above.

If anybody can advise me I would really appreciate it.

Cheers

 

[Remou]

Have you tried:

[tt]>date()+5[/tt]

Or

[tt]>DateAdd("d",5,Date())[/tt]

 

[AlexCuse]

I think you are looking for this:

>DateAdd("d",-5,Date())

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[pmaths]

Hi - thanks for the swift responses.

Unfortunatly those suggestions return all records after the current date - it would appear that the bit after >Date or >DateAdd is being ignored...

 

[AlexCuse]

Or you are storing Dates in a text column?

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Or if you want 5 days on either side of current date:

where datecol >DateAdd("d",-5,Date())
and datecol < DateAdd("d", 5, Date())

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

It is a little odd, too, that Date()+5 (or -5) does not work. It is commonly used in Access queries.

 

[AlexCuse]

How true. Pmaths, can you post the SQL for your entire query?

Ignorance of certain subjects is a great part of wisdom

 

[pmaths]

Guys - thanks for you help on this - I have found the problem. Basically our Quality dept have had a database written by an external company and the date field I'm looking at has been given a "text" data type. I've just changed it to Date/Time and guess what? All is good...

So basic a problem I just did'nt think about it...

Cheers again guys - I really appreciated your responses.

Pete.