0

I'm getting started with databases in Access for a work project (technically a work-learn for my non-CompSci degree). I have experience using multiple filters on multiple columns through Access/Excel, but for my work learn I'm trying to design an interface with some "quick filter" functionality built into the UI. Since I'm pretty green, I'm using macros instead of VBA which seems to be doing the job fine... But I've hit a snag, and was hoping y'all might be able to help?

I have a split-view form with top containing record details and quick search options, and the bottom containing the table itself as filtered (which serves as a list of results). What I'm trying to do is filter a column by the selections from two dropdowns on a form.

I was able to do this by doing:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*"

But then I realized that while that will find entries where IntCombo1 and IntCombo2 appear in order, (e.g. Dog, Cat) it won't show ones where they appear reversed (Cat, Dog).

I would think that the way to fix that would be one of:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*"

or

ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*") OR ("*"IntCombo2 & "*" & IntCombo1 "*")

or

 ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*")

But none of these work, and I'm not sure why because they all seem pretty boolean-ly sound. Can someone tell me what I'm doing wrong?

(Other perhaps relevant info is that the entries on the table I'm filtering are short text words/phrases separated by semicolons ("Dog; Cat; Mouse; Horse", "Mouse", "Cat; Dog", etc...). I totally own that this should probably be converted into a table (long term goal), but the dataset was created over 5 years in an Excel spreadsheet, and therefore is a beast so I haven't gotten around to doing that yet.)

0

Oh.

Well, I figured it out. I didn't realize that when I use OR, the macro parses it like WHERE (X LIKE Y) OR (X Like Z).

The solution is as follows:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR Table!Articles!IntCodes LIKE "*"IntCombo2 & "*" & IntCombo1 "*"

I was hoping to be a little more efficient, but apparently macros don't dig that. :( Any other comments still appreciated!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.