I am trying to have a criteria of

Between Date()-7 and Date()-3 in my query, however, it isn't returning any data. I also have tried using datediff, but without any luck. Could someone shed some light on what I may be doing wrong?

Thanks

Try:

Between DateADD("dd",-7,Date()) AND DateADD("dd",-3,Date())

OR DateDiff([YourDate],Date()) Between 3 and 7

Off the top of me head so may need slight tweaking

Thanks for the response FoFa, however, it isn't working for me.

Just a little background why I need this.

I am trying to run this query so that a user won't have to enter anything manually. I am going to have this running on our server every week for the prior week's data. That is why I am trying to get this date criteria to work.

Thanks for any help.

Hi -

What is the data type of your date field? Should be date/time. If dates are stored as text, that could be the problem.

Bob

I have my tables linked to a Cache database; and yes, I believe the fields are stored as text. Is there a workaround for this like date(format(Date,MM-DD-YYYY)-7)?

Thanks for your reply.

Hi -

Format() just restructures the display--a text field still remains a text field.
Lookup the DateValue() function. Try adding a calculated field to your query, e.g. MyDate: datevalue([yourDate]), then apply your criteria to the calculated field.

See this MSKB article on how dates are stored in Access:

http://support.microsoft.com/kb/q130514/

I got my query to work by creating a make table query with the dates that I needed; then, I linked a form to that tblDate table and hid the date criteria. Then on my original query, I wrote the criteria as follows:

between[forms]![form1]![txtStartDate] and [forms]![form1]![txtEndDate]

Have a great day.