[Ask Me Parameters] Access 2007

[Ron]

I have a query where I Ask for Parameters i.e. [Enter Year as yyyy]
This works OK if I want Year "2007"

If I want All Years, In previous version of Access, I could Enter Wildcard
* to get all years. This does not work in Access 2007

I'd like to use one query instead of two.

 

[Lord Kelvan]

your criteria need to be

like [Enter Year as yyyy]

you should never be able to do '*' on a date field without 'like' you
have to use 'like' when you use a wild card for any version of access

regards
kelvan

 

[Allen Browne]

The wildcard performs a string comparison, which is dependent on unreliable
factors such as how the field is formatted.

It would be much more reliable, and quicker to execute (assuming the date
field is indexed) if you ask for the year as a number, and use DateSerial()
to generate the date range for the year:

In the Criteria row, enter this (as one line):

= DateSerial([Enter Year as yyyy],1,1) And <

DateSerial([Enter Year as yyyy]+1,1,1)

Then choose Parameters (Query menu.)
Access opens a dialog.
Enter:
[Enter Year as yyyy] Integer

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query where I Ask for Parameters i.e. [Enter Year as yyyy]
This works OK if I want Year "2007"

If I want All Years, In previous version of Access, I could Enter Wildcard
* to get all years. This does not work in Access 2007

I'd like to use one query instead of two.

 

[John Spencer]

An alternative to handling this problem is to use the following and do not
enter anything when prompted if you want to get all records that have a value
in your year field.

Field: YourYearField
Criteria: Between Nz([Enter Year as yyyy],1800) and Nz([Enter Year as yyyy],9000)

Or
Field: YourYearField
Criteria: [Enter Year as yyyy] or [Enter Year as yyyy] is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson/MVP]

I have a query where I Ask for Parameters i.e. [Enter Year as yyyy]
This works OK if I want Year "2007"

If I want All Years, In previous version of Access, I could Enter Wildcard
* to get all years. This does not work in Access 2007

I'd like to use one query instead of two.

Try a criterion of

[Enter year as yyyy, leave blank for all] OR [Enter year as yyyy,
leave blank for all] IS NULL

to have a blank entry return all records.