How do I do a query with the criteria being all values in a combo box?

[Seth Schrock]

I'm making a directory database for a local church. They keep track of members as well as non-members. Currently I have a combobox set as the criteria for a query to list either the members or the non-members (the two options in the combobox). Is there a way to make it so that the same combobox will have an "all" option so that everyone in the database will be listed? That way the user has the option to search for all members, all non-members, or everyone (members and non-members) all out of the same combobox.

[patjones]

How does the query interact with the combo box currently? For example, is it a query that you built in design view with a parameter, or is it a SQL statement embedded in VBA with the combo box value inserted into the WHERE clause?

Also, what is the column that determines membership status and what data type is it holding?

Pat

[Seth Schrock]

It is a query done in design view with the combo box in the criteria.

The membership status field is a number field related to tblMemberStatus . So the criteria is a number. Currently there is only two values 1, member and 2, non-member but I want to be able to add others such as member-in college, or something like that. In other words, it needs to be flexible and not hard coded to put in the criteria of 1 and 2. Make sense?

[NeoPa]

You can certainly do that Seth, but how you currently do the filtering is important information in the question, as there are many possible ways. We don't know which approach you're using unless you tell us in your question.

[Seth Schrock]

Currently, the query is filtered only using the combo box on the main form. The query is run from the AfterUpdate event of the combo box. The MemberStatus field in the query has the criteria of

[Forms]![frmMain]![cmbDirectorySearch]

cmbDirectorySea rch is bound to a number field.

Is that the information that you need?

[NeoPa]

I think I can piece it all together with the various bits of information now. I'm not sure the design allows for easy management this way, but that's another question.

In your ComboBox, you could allow for a value 0 which may display as "All". In your query (The code of which would have been a good thing to include in the question BTW), instead of including something in your WHERE clause like :

[X] = [Forms]![frmMain]![cmbDirectorySearch]

You would have instead :

[Forms]![frmMain]![cmbDirectorySearch] In(0,[X])

This would be in the SQL of the query BTW. See Extracting SQL from a QueryDef for how to get that in case you don't already know.

[Seth Schrock]

Here is the query that I have right now:

SELECT tblMembers.MemberLastName, tblMembers.MemberFirstName, tblFamily.FamilyName, tblMembers.FamilyStatus, tblMembers.Address, ([tblMembers]![City] & ', '+[tblMembers]![State] & '  '+[tblMembers]![Zip]) AS CityStateZip, tblMembers.MemberBirthday, tblFamily.Phone, tblMembers.MemberCellPhone
FROM tblFamily INNER JOIN tblMembers ON tblFamily.FamilyID = tblMembers.FamilyID
WHERE (((tblMembers.MemberStatus)=[Forms]![frmMain]![cmbDirectorySearch]))
ORDER BY tblMembers.MemberLastName, tblFamily.FamilyName, tblMembers.FamilyStatus, tblMembers.MemberBirthday;

and this is what I changed the WHERE statement to:

WHERE [Forms]![frmMain]![cmbDirectorySearch] In (0, MemberStatus)

How do I allow for a value of 0 in the combo box? Do I need to add a record to the tblMemberStatus table with the PK of 0 and the text as "All"?

[NeoPa]

Seth:
How do I allow for a value of 0 in the combo box? Do I need to add a record to the tblMemberStatus table with the PK of 0 and the text as "All"?

Yes!

Assuming the ComboBox is populated from a table, and I think you already said somewhere it is, that's exactly what you do.

[Seth Schrock]

Works beautifully! Thanks.

[NeoPa]

Good to hear Seth. That's not the type of solution you're likely to see very often, but it's worth remembering the concept. The left hand operator needn't necessarily be the field in the record.

[patjones]

Wow, that's great. A new concept for me, and one well worth remembering. Thanks.

[NeoPa]

Thanks for that Pat. Especially pleased to help other experts :-)