Filtering Null values from a Multi Select List box used to generate query

  • Thread starter Thread starter BWD
  • Start date Start date
B

BWD

Hello Group!

I am working with a form that has 4 list boxes with multi select
enabled. The multi selects are used in a query that is coded into the
reports on open event. The query uses an "IN" statement to filter the
results based upon the values selected from the 4 list boxes. If one
of the "IN" statements (list boxes) is null (no values selected) then
the entire query fails and the report doesn't run properly.

I need a method to verify that none of the 4 list boxes have null
values (no value selected from the list) and if they do, remove the IN
statement associated with the list box(es) that have a null value.

So specifically I have 2 questions: First, I am not sure what code to
use to check for Nulls. I can not simply set my list box value to say
"If isnull(listbox) then" because this method produces bogus logic.

Secondly, once I have a test to verify if the value is null, how do I
remove the associated "IN" statement from the query being used as the
record source for the report? I am sure that I can write various Case
statements for each possibility but that would be cumbersome and I am
thinking there must be a sleeker way of going about this task.

Any help would be appreciated.

Cheers!
 
Hello Group!

I am working with a form that has 4 list boxes with multi select
enabled. The multi selects are used in a query that is coded into the
reports on open event. The query uses an "IN" statement to filter the
results based upon the values selected from the 4 list boxes. If one
of the "IN" statements (list boxes) is null (no values selected) then
the entire query fails and the report doesn't run properly.

I need a method to verify that none of the 4 list boxes have null
values (no value selected from the list) and if they do, remove the IN
statement associated with the list box(es) that have a null value.

So specifically I have 2 questions: First, I am not sure what code to
use to check for Nulls. I can not simply set my list box value to say
"If isnull(listbox) then" because this method produces bogus logic.

Secondly, once I have a test to verify if the value is null, how do I
remove the associated "IN" statement from the query being used as the
record source for the report? I am sure that I can write various Case
statements for each possibility but that would be cumbersome and I am
thinking there must be a sleeker way of going about this task.

Any help would be appreciated.

Cheers!
Click to expand...

if all you want to know is whether any values are selected, use
something like

If Me.List0.ItemsSelected.Count=0 then
'don't process the listbox
Else
.....
End If
 
if all you want to know is whether any values are selected, use
something like

If Me.List0.ItemsSelected.Count=0 then
'don't process the listbox
Else
....
End If- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, that helped. I simply creted the test based on a count value
less than 1.
 
Back
Top