Parameter Query: Blank inputs?

B

Brett

Hello All,

I am creating a database for my boss who is not very access savy. As a
result, I need a very user-friendly system so he or anyone else at work can
use it. I have a parameter query set up right now which asks for several
criteria. For example, one of the prompts has the following criteria in the
Design View:

Between [Enter Risk Rating (Lower Range - 1):] And [Enter Risk Rating (Upper
Range - 7):]

I would like to set this Between...And prompt to allow blank inputs because
as of right now, anything left blank in the prompts will return no records
when the query is finished running.

Any help would be greatly appreciated.

Thank you,

Brett
 
O

Ofer

For all the criteria you cna use the nz function to replace the null value
with any value that returns all records

For between
[MyField] Between nz([Enter Risk Rating (Lower Range - 1):],#1/1/1000#) And
nz([Enter Risk Rating (Upper Range - 7):],#1/1/2200#)
So if no data is entered it will display all the records between 1/1/1000
and 1/1/2200

If you want to compare one field then use the nz with like

[MyField] Like nz([Enter A Value Here:],"*")
If no value enter it will take all the records
 
V

Van T. Dinh

Not sure of your set-up but try:

BETWEEN Nz([Enter Risk Rating (Lower Range - 1):], 1)
AND Nz([Enter Risk Rating (Upper Range - 7):], 7)
 
Top