0

If anyone can point me in the right direction I would greatly appreciate it.

I am making an Asset Management database. I'm looking at having about 50 fields in a table. Of those 50, 25 could potentially be used for searching. In some cases multiple fields would be used to narrow the scope of a search.

I have created a query for each of those 25 fields and set it to group by so I have a list of unique values for those fields. I then made a form with combo boxes based off those queries. My thinking is now on 1 form I have 25 combo boxes and a user can select what they want to sort by, by choosing it from the combo boxes.

I made another query that references all of those combo boxes as criteria for the fields.

This of course doesnt work and I can't figure out a way to ignore fields the user does not use to filter the data. The query always comes up empty. Is there a way to ignore unused fields? I set a default value for each of the combo boxes as "Choose Filter" but I can't figure out how to have Access ignore the values listed as "Choose Filter" while filtering based off the used fields.

I have tried to look up stuff about cascading combo boxes, but I do not think that will work for me because the user might use different fields each time they do a search.

I do not use SQL so if that is the solution I might need more help.

Thank you.

1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by HI2Japan
0

Found a solution that works. I should have figured it would be easy.

IIf(IsNull([forms]![frmFilter]![DeptCombo]),[Department],[forms]![frmFilter]![DeptCombo])

I put this as a criteria for each of the fields in the main query substituting the combo box field.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.