List Box Paramater query with Null value

  • Thread starter Thread starter popejackson
  • Start date Start date
P

popejackson

My form has 3 list boxes that select values for a query with a command button
to run the query. The query criteria is [forms]![form1]![listbox1] for each
listbox.

IE: listbox1 - Name, listbox2 - Location, listbox3 - AccountType

I would like to search by one or all of these criteria.

The problem that I have is when I do not select a value from one of the
listbox controls my query returns nothing, although I have selected a valid
value from one or more of the listboxes.

How can I word the query field criteria to work in this case. Basically
ignore the criteria of [forms]![form1]![listbox1] when a null value exists in
the listbox on the form.

Thanks in advance for any help that may be offered. Very new to this and
hacking my way along atm.
 
I did find an answer from an older post : Like "*" &
[forms]![form1]![listbox1] & "*" in the query criteria.
 
There's a subtle difference between that and my suggestion that may or may
not matter to you.

What you've got will only return values for those rows where the field in
question has a value: it will ignore any rows where that field has a Null
value in it.

My suggestion will return all rows, regardless of the value in that field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


popejackson said:
I did find an answer from an older post : Like "*" &
[forms]![form1]![listbox1] & "*" in the query criteria.





popejackson said:
My form has 3 list boxes that select values for a query with a command
button
to run the query. The query criteria is [forms]![form1]![listbox1] for
each
listbox.

IE: listbox1 - Name, listbox2 - Location, listbox3 - AccountType

I would like to search by one or all of these criteria.

The problem that I have is when I do not select a value from one of the
listbox controls my query returns nothing, although I have selected a
valid
value from one or more of the listboxes.

How can I word the query field criteria to work in this case. Basically
ignore the criteria of [forms]![form1]![listbox1] when a null value
exists in
the listbox on the form.

Thanks in advance for any help that may be offered. Very new to this and
hacking my way along atm.
Click to expand...
Click to expand...
 
Great - Thanks for your help.

Douglas J. Steele said:
There's a subtle difference between that and my suggestion that may or may
not matter to you.

What you've got will only return values for those rows where the field in
question has a value: it will ignore any rows where that field has a Null
value in it.

My suggestion will return all rows, regardless of the value in that field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


popejackson said:
I did find an answer from an older post : Like "*" &
[forms]![form1]![listbox1] & "*" in the query criteria.





popejackson said:
My form has 3 list boxes that select values for a query with a command
button
to run the query. The query criteria is [forms]![form1]![listbox1] for
each
listbox.

IE: listbox1 - Name, listbox2 - Location, listbox3 - AccountType

I would like to search by one or all of these criteria.

The problem that I have is when I do not select a value from one of the
listbox controls my query returns nothing, although I have selected a
valid
value from one or more of the listboxes.

How can I word the query field criteria to work in this case. Basically
ignore the criteria of [forms]![form1]![listbox1] when a null value
exists in
the listbox on the form.

Thanks in advance for any help that may be offered. Very new to this and
hacking my way along atm.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top