Criteria for "All" in a query

[Guest]

I know that the wildcard Like "*" returns all value. But I am having trouble
referencing it in the criteria of a query. I have a form with listboxes. When
the user selects a value in a listbox it places the value in a field on the
form. If the user does not make a selection in that a particular listbox the
vale defaults to the string "All". I then have the following in the criteria
of the query.

IIf([Forms]![fMainReporting]![selectDept]="All",Like
"*",[Forms]![fMainReporting]![selectDept])

I have written the syntax for the wildcard everyway I can think of; nothing
works. If there is a Dept value in [selectDept] the queries display data
fine; if the value in [selectDept] is "All" nothing is returned. It should
return all records.

Any help would be greatly appreciate.

 

[fredg]

I know that the wildcard Like "*" returns all value. But I am having trouble
referencing it in the criteria of a query. I have a form with listboxes. When
the user selects a value in a listbox it places the value in a field on the
form. If the user does not make a selection in that a particular listbox the
vale defaults to the string "All". I then have the following in the criteria
of the query.

IIf([Forms]![fMainReporting]![selectDept]="All",Like
"*",[Forms]![fMainReporting]![selectDept])

I have written the syntax for the wildcard everyway I can think of; nothing
works. If there is a Dept value in [selectDept] the queries display data
fine; if the value in [selectDept] is "All" nothing is returned. It should
return all records.

Any help would be greatly appreciate.

The Like word must come first.
Like IIf([Forms]![fMainReporting]![selectDept]="All","*",
[Forms]![fMainReporting]![selectDept])

 

[Guest]

Try this

Like IIf([Forms]![fMainReporting]![selectDept]="All",
"*",[Forms]![fMainReporting]![selectDept])

 

[Guest]

This code worked perfect! I do not understand putting the "Like" in front,
but it sure works so I am just going to accept it. Thanks a million! -Charles

I know that the wildcard Like "*" returns all value. But I am having trouble
referencing it in the criteria of a query. I have a form with listboxes. When
the user selects a value in a listbox it places the value in a field on the
form. If the user does not make a selection in that a particular listbox the
vale defaults to the string "All". I then have the following in the criteria
of the query.

IIf([Forms]![fMainReporting]![selectDept]="All",Like
"*",[Forms]![fMainReporting]![selectDept])

I have written the syntax for the wildcard everyway I can think of; nothing
works. If there is a Dept value in [selectDept] the queries display data
fine; if the value in [selectDept] is "All" nothing is returned. It should
return all records.

Any help would be greatly appreciate.

The Like word must come first.
Like IIf([Forms]![fMainReporting]![selectDept]="All","*",
[Forms]![fMainReporting]![selectDept])

 

[klkropf]

This works great, but what if you want it to return blank fields also?

This code worked perfect! I do not understand putting the "Like" in front,
but it sure works so I am just going to accept it. Thanks a million! -Charles

I know that the wildcard Like "*" returns all value. But I am having trouble
referencing it in the criteria of a query. I have a form with listboxes. When
the user selects a value in a listbox it places the value in a field on the
form. If the user does not make a selection in that a particular listbox the
vale defaults to the string "All". I then have the following in the criteria
of the query.

IIf([Forms]![fMainReporting]![selectDept]="All",Like
"*",[Forms]![fMainReporting]![selectDept])

I have written the syntax for the wildcard everyway I can think of; nothing
works. If there is a Dept value in [selectDept] the queries display data
fine; if the value in [selectDept] is "All" nothing is returned. It should
return all records.

Any help would be greatly appreciate.

The Like word must come first.
Like IIf([Forms]![fMainReporting]![selectDept]="All","*",
[Forms]![fMainReporting]![selectDept])

 

[Douglas J. Steele]

Rather than

Like
IIf([Forms]![fMainReporting]![selectDept]="All","*",[Forms]![fMainReporting]![selectDept])

use

= [Forms]![fMainReporting]![selectDept] Or
([Forms]![fMainReporting]![selectDept] = "All")



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This works great, but what if you want it to return blank fields also?

This code worked perfect! I do not understand putting the "Like" in
front,
but it sure works so I am just going to accept it. Thanks a
million! -Charles

On Mon, 16 Jan 2006 08:31:06 -0800, cbjames wrote:

I know that the wildcard Like "*" returns all value. But I am having
trouble
referencing it in the criteria of a query. I have a form with
listboxes. When
the user selects a value in a listbox it places the value in a field
on the
form. If the user does not make a selection in that a particular
listbox the
vale defaults to the string "All". I then have the following in the
criteria
of the query.

IIf([Forms]![fMainReporting]![selectDept]="All",Like
"*",[Forms]![fMainReporting]![selectDept])

I have written the syntax for the wildcard everyway I can think of;
nothing
works. If there is a Dept value in [selectDept] the queries display
data
fine; if the value in [selectDept] is "All" nothing is returned. It
should
return all records.

Any help would be greatly appreciate.

The Like word must come first.
Like IIf([Forms]![fMainReporting]![selectDept]="All","*",
[Forms]![fMainReporting]![selectDept])