Query to find criteria then run

[maw230]

I need my sales query to search an existing table for the highest instance of Date_ID, and then run the query for all dates between the day after Date_ID and yesterday.

Will I need VBA to look up and store this value? Either way, how can I do this?

 

[pbaldy]

Try a criteria of

Between DMax("TableName", "Date_ID") + 1 And Date() - 1

 

[maw230]

Try a criteria of

Between DMax("TableName", "Date_ID") + 1 And Date() - 1

Just had to switch TableName and Date_ID and it worked perfectly! Thank you.

Between DMax("Date_ID", "TableName") + 1 And Date() - 1

 

[pbaldy]

Oops! That was a brain cramp.

Glad you got it sorted out.

 

[maw230]

So, this works great as I said before. However, I forgot to mention that I am using this for a Year-to-date query i.e. I need it to return the same dates for Last Year as well.

This is something I've been meaning to learn for a while - How can I take a Date field record and return the same Date, but for last year to be used in the criteria field of the same or another query?

 

[pbaldy]

You should be able to find relevant functions here:

http://www.pacificdb.com.au/Support/code_datetime.htm