Using Multislect on feilsd with null value

[cwby1966]

I have a query that gets its criteria from a form with 4 multiselct list boxs.
The problem i have is that some of the freilds can have null value. The
fields that I am using are [Site id], [Primary function], Secondary_function],
and [tertiarty fuctin] teh Second and Ter funcs may have null value, when you
select multi feilds and multiple items teh query doe snot produce the desired
results. exmaple, if you select a specfic location, and gyms in the primary
fuction and run the query, you do not get all of tthe areas that are
classified as gym, there are 5 entries and 3 have not secondary ot tertariy
functions and these do not appear in the query.
Can you use multi slect list box on feilds that have null values?
Thansk

 

[Allen Browne]

If you are filtering 3 fields, presumably you want any record that matches
on any one of the 3 fields. The fact that the others could be Null is
therefore irrelevant.

The expression you will be seeking to generate will have to end up like
this:
([Primary function] IN (1, 3, 8)) OR ([Secondary function] IN (1, 3, 8))
OR ([Tertiarty function] IN (1, 3, 8))

Since it's the same expression repeated 3 times, you would use the same
logic as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Hopefully you are aware that this is not the ideal way to build your table.
If a site has multiple functions, you could be better off using a related
table to hold the details of the functions applicable to the site.

 

[cwby1966 via AccessMonster.com]

Thanks for the advice. Unfortunilty this did not work either for my situation.
A site will not have multiple functions, Primary functin is the high level,
Secondary is second level and tertiarty is third. Example: Prim is Classroom,
Sec is Lab, Tert is Chemistry,

If you are filtering 3 fields, presumably you want any record that matches
on any one of the 3 fields. The fact that the others could be Null is
therefore irrelevant.

The expression you will be seeking to generate will have to end up like
this:
([Primary function] IN (1, 3, 8)) OR ([Secondary function] IN (1, 3, 8))
OR ([Tertiarty function] IN (1, 3, 8))

Since it's the same expression repeated 3 times, you would use the same
logic as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Hopefully you are aware that this is not the ideal way to build your table.
If a site has multiple functions, you could be better off using a related
table to hold the details of the functions applicable to the site.

I have a query that gets its criteria from a form with 4 multiselct list
boxs.

[quoted text clipped - 13 lines]

Can you use multi slect list box on feilds that have null values?
Thansk

 

[Allen Browne]

I don't think I understand your question here.

Based on the subject line, perhaps you want:
([Primary function] IN (1, 3, 8)) OR ([Primary function] Is Null)
OR ([Secondary function] IN (1, 3, 8)) OR ([Secondary function] Is Null)
OR ([Tertiarty function] IN (1, 3, 8)) OR ([Tertiarty function] Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the advice. Unfortunilty this did not work either for my
situation.
A site will not have multiple functions, Primary functin is the high
level,
Secondary is second level and tertiarty is third. Example: Prim is
Classroom,
Sec is Lab, Tert is Chemistry,

If you are filtering 3 fields, presumably you want any record that matches
on any one of the 3 fields. The fact that the others could be Null is
therefore irrelevant.

The expression you will be seeking to generate will have to end up like
this:
([Primary function] IN (1, 3, 8)) OR ([Secondary function] IN (1, 3,
8))
OR ([Tertiarty function] IN (1, 3, 8))

Since it's the same expression repeated 3 times, you would use the same
logic as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Hopefully you are aware that this is not the ideal way to build your
table.
If a site has multiple functions, you could be better off using a related
table to hold the details of the functions applicable to the site.

I have a query that gets its criteria from a form with 4 multiselct list
boxs.

[quoted text clipped - 13 lines]

Can you use multi slect list box on feilds that have null values?
Thansk