Member Avatar for HI2Japan

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
  • 128 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by HI2Japan
Member Avatar for HI2Japan

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.