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

[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!

 

[pietlinden]

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!

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

 

[BWD]

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 -

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