Select Multiple criteria for query on a form (1 Viewer)

[WillEllis]

I was searching through here looking for a solution to the following problem:
I am building a Capacity database for work. The requirements require that users be able to sort by numerous criteria (Forecast Date, Portfolio, Market, Bucket, Month), all from a simple form that will spit out the results. Rather than creating unique queries for each combination of criteria(way too many!) or creating the SQL text in VBA, I played around until I came up with the following(which may have been done already, but I can't find it on here, so I'm not claiming to be brilliant...LOL). I have dropdowns on my form for each sort criteria-(Forecast Date, Portfolio, Market, Bucket, Month)

Then I created a query and for each criteria, I put this in:
Like (IIf(IsNull([Forms]![frmWAOFAdjustments]![Bucket]),"*",[Forms]![frmWAOFAdjustments]![Bucket]))

So if the user leaves a dropdown blank, the query simply brings back all the results from that field (Like *).

The text in bold is simply replaced with the dropdown name for each segmentation criteria. Next to each dropdown is a button that clears the dropdown box and requeries the listbox with the results(in case the user doesn't want to remove a criteria. This makes running searches for a specific number of accounts easy for the user and easy on me..lol.

Hopefully this can help someone out with a similar problem. I have learned so much from this forum, I just wanted to give something back. If anyone has any questions on this, just let me know.

 

[steallan]

I love you!

Just spent an hour looking through the threads trying to find the answer to this exact problem. Works a treat

All Hail WillEllis, HAIL! HAIL! HAIL!

er....i mean thanks

 

[taylorhouse]

Wow if only it was 5 years ago. But i will ask a question any way.

This is what i am struggling with and am using the same method but in a slightly different way.

The problem i have is that when you have

Like *

It does not show the records that have Null in that field

I need it for be

Like * Or Is Null

But what ever i do the Or Is Null is seen as Text and is search for

Anyone got any ideas

Cheers

 

[jbroge]

This does exactly what I have been trying to accomplish! Thanks for sharing!