Parameters

[Reese]

When using parameters: If the user wants all records to be returned, without
the filtering that the parameter was set up for, is there a word, such as
"All" or "Any" that could be entered in the parameter dialogue box to get
that result... basically negate the parameter without deleting it? Would
that have to be accounted for in the expression?

 

[KARL DEWEY]

The expression must be written differently.
There are several ways to do it. One way is like this --
Like [Enter parameter] & "*"
In this case if at the prompt the user just presses the ENTER key.

You could write it for user to enter ALL in this way --
Like IIF([Enter parameter] = "ALL", "*", [Enter parameter])

 

[John W. Vinson]

When using parameters: If the user wants all records to be returned, without
the filtering that the parameter was set up for, is there a word, such as
"All" or "Any" that could be entered in the parameter dialogue box to get
that result... basically negate the parameter without deleting it? Would
that have to be accounted for in the expression?

I'll have to disagree with Karl here: the * wildcard will not find NULL
values, and may not work as desired for datatypes other than text.

Try a criterion of

= [parameter] OR [parameter] IS NULL

If the user leaves the parameter blank, it will find all records.

If you wish, you could use

= [parameter] OR ([parameter] = "ALL")

to allow the user to enter or select (from a combo box, say) ALL.

 

[Reese]

Thank you. I'm having some trouble getting it to work. Here's my
expression:

Like [Enter 1st 3 Letters of Model] & "*"

How would I apply your solution to that expression without messing up the
original expression?

Thanks,
Reese

When using parameters: If the user wants all records to be returned, without
the filtering that the parameter was set up for, is there a word, such as
"All" or "Any" that could be entered in the parameter dialogue box to get
that result... basically negate the parameter without deleting it? Would
that have to be accounted for in the expression?

I'll have to disagree with Karl here: the * wildcard will not find NULL
values, and may not work as desired for datatypes other than text.

Try a criterion of

= [parameter] OR [parameter] IS NULL

If the user leaves the parameter blank, it will find all records.

If you wish, you could use

= [parameter] OR ([parameter] = "ALL")

to allow the user to enter or select (from a combo box, say) ALL.

 

[Reese]

Thanks Karl. I'm using that exact expression so that I can enter the first 3
letters of the model I'm looking for and Access will return any record with
those first 3 letters. That works just fine, but if I just hit "enter"
without putting in the first 3 letters, I get no returns. This is my
expression:

Like [Enter 1st Three Letters of Model] & "*"

What I'm looking to do is to "Enter Through" the dialogue box (leave it
blank) and get all records to return.

The expression must be written differently.
There are several ways to do it. One way is like this --
Like [Enter parameter] & "*"
In this case if at the prompt the user just presses the ENTER key.

You could write it for user to enter ALL in this way --
Like IIF([Enter parameter] = "ALL", "*", [Enter parameter])

--
Build a little, test a little.

When using parameters: If the user wants all records to be returned, without
the filtering that the parameter was set up for, is there a word, such as
"All" or "Any" that could be entered in the parameter dialogue box to get
that result... basically negate the parameter without deleting it? Would
that have to be accounted for in the expression?

 

[Bob Barrows]

Exactly as he said. If you wish the user to enter the word "All" to get all
the records:
Like [Enter 1st 3 Letters of Model] & "*" OR [Enter 1st 3 Letters of Model]
= "All"

or, it you wish the user to enter the word "any":
Like [Enter 1st 3 Letters of Model] & "*" OR [Enter 1st 3 Letters of Model]
= "Any"

Thank you. I'm having some trouble getting it to work. Here's my
expression:

Like [Enter 1st 3 Letters of Model] & "*"

How would I apply your solution to that expression without messing up
the original expression?

Thanks,
Reese

When using parameters: If the user wants all records to be
returned, without the filtering that the parameter was set up for,
is there a word, such as "All" or "Any" that could be entered in
the parameter dialogue box to get that result... basically negate
the parameter without deleting it? Would that have to be accounted
for in the expression?

I'll have to disagree with Karl here: the * wildcard will not find
NULL values, and may not work as desired for datatypes other than
text.

Try a criterion of

= [parameter] OR [parameter] IS NULL

If the user leaves the parameter blank, it will find all records.

If you wish, you could use

= [parameter] OR ([parameter] = "ALL")

to allow the user to enter or select (from a combo box, say) ALL.

 

[John W. Vinson]

Thank you. I'm having some trouble getting it to work. Here's my
expression:

Like [Enter 1st 3 Letters of Model] & "*"

How would I apply your solution to that expression without messing up the
original expression?

If the user enters nothing at all in the textbox, this will translate to

Like "*"

which will return all records.

You don't need to do *anything at all* (other than some simple user training).