Filtering a subform using multple criteria

[Jeff Harbin]

I have a subform that is generated from a table. The fields on the form
are listed below:

Selected (Yes/No Field type)
Description (Text Field)
Building1 (Yes/No Field)
Building2 (Yes/No Field)
Building3 (Yes/No Field)
Building4 (Yes/No Field)
Building5 (Yes/No Field)
Building6 (Yes/No Field)

A lot of the entries have multiple checkboxes in the building locations
(ie., Description A might have a checkbox in Building1, Building4, and
Building5 fields.)

I've got a drop down box on my main form that has the 6 Building
locations as options. If the user selects Building1 from the main
form's drop down box, I would like the subform to display only the
entries that have a checkbox in the Building1 field.

I know you can base the subform on a query using the dropdown as a
criteria in the query but the subform isn't set up that way. The
Building numbers aren't a value in a field they are the names of the
fields so I'm a little stuck.

Can someone give me a little push towards solving this?

Thanks,

Jeff

 

[Michel Walsh]

Hi,


SELECT *

FROM myTable

WHERE SWITCH( FORMS!FormNameHere!ComboBoxNameHere = "Building1", Building1,
..., ...
FORMS!FormNameHere!ComboBoxNameHere = "Building6",
Building6)



Hoping it may help,
Vanderghast, Access MVP

 

[Jeff Harbin]

Thanks Michel. I'm working with that right now. I've never used the
SWITCH command before. I knew it existed in EXCEL but didn't know it
was an option in ACCESS.

Thanks