query with speciific dates

[Pasi]

datequery.docHI,

I run a query each week for previous week. example" I have to run a query for today 12/30/13 and the query should run from 12/23/13 to 12/30/13. what can I put into expression to do this automatically so I don't have to open the query input the date and run it? I am trying to automate this so next week when I come back its already know that I need to run this query for past 7 days and just run the query.
Thanks,
Pasi.



I have added attachment.

[pbaldy]

Try a criteria of:

Between Date() - 7 And Date()

[Pasi]

Thanks Pbaldy but it did not work? any other suggestion?

[Bob Fitz]

Perhaps something like:
Between DateDiff("d",-7, Date()) and Date()

[Pasi]

Thanks BOb, Its not working? I know its something simple but I cant figure it out?

[Bob Fitz]

Thanks BOb, Its not working? I know its something simple but I cant figure it out?

"It's not working" doesn't really help us to help you.
What result do you get?
Can you show us the SQL of your query.

[Pasi]

When I run it it just hangs and nothing happens? when I give a range like: Between "20131201" And "20131208" executes fast and I get a result fast.

[pbaldy]

Is your field text rather than date/time? If so, why?

[Pasi]

No its date Date field.doc no its date field see attached.
Thanks!

[Pasi]

This is how I am doing it:

[dbo_appointments1]![appt_date] Between Date() And Date()-"6"

[pbaldy]

Your attachment doesn't indicate the data type of the field. Why do you have quotes around the 6? What's the SQL of the query?

[Pasi]

when I execute the query it puts quotes around number. not sure what you mean by sql of query?

[pbaldy]

You can change the view when in design view to SQL view, which would look like

SELECT...

If it's putting quotes around the number, it's confused about the data type of the field.

[Pasi]

OK I ma in Select view see attached.query view.doc

[Pasi]

Here is what I changed it to: DateAdd("d",-7,[appt_date])