Criteria for choosing dates.

[Hans]

I'm trying to have a query ask for a week ending date, and give data for the date and 6 days previous. Therefore in criteria of Date column I say "Between [Week ending Sunday] and ([week ending Sunday}-6)". Do you see what I am trying to do? Shouldn't this or something close to it work?

 

[Alexandre]

1. Declare the parameter: Week ending Sunday as Date/Time from the menu of in the QBE grid.

2 Use the criteria Between [Week ending Sunday] and ([week ending Sunday]-6).

It will work provided that the user entered a valid date for [Week ending Sunday].

Alex

[This message has been edited by Alexandre (edited 02-21-2002).]

 

[Hans]

Thanks so much! I hadn't declared the parameter datetime.

Not sure why I had to add it this time and not other times when I simply put a [What date?] in criteria and it ran ok?

 

[David R]

I believe you can also put the literal "#"'s in the criteria section, not as part of the parameter but just outside the brackets.

David R

 

[Alexandre]

Hans, the reason is that when you declare your parameter like that, it can be used as a "variable" as many times in you query as you want.
It is different from when access 'stumbles across a label' in your SQL statement, that it inteprets as a parameter. It then asks for the value it needs to proceed but do not store the result as a vraiable as with a declared parameter, and you cannot re-use that value.

Alex

[This message has been edited by Alexandre (edited 02-21-2002).]

 

[David R]

At least for Acc2k, that is not necessarily true. You can reuse a parameter without explicitly declaring it, and even use it in the report the query is based upon.
I thought the problem was more related to Access not understanding dates that were not explicitly defined as dates through some fashion.

David R