How do I put a wildcard into the truepart of an IIf statement of an access query criteria?

February 11, 2009 7:32 AM

How do I put a wildcard into the truepart of an IIf statement of an MS Access 2003 query criteria?

Unfortunately, I cannot make changes to the data structure or tables because my company uses a strictly-controlled data entry frontend (iMIS).

Here's what I'm trying to do: use an option group on a form to pick criteria 1, 2 or both. I have this in my query criteria currently:

IIf([Forms]![Simple Selector]![OptGrpMemType]=1,"MRD",IIf([Forms]![Simple Selector]![OptGrpMemType]=2,"NRD",IIf([Forms]![Simple Selector]![OptGrpMemType]=3,X,)

For X I have tried:
Like "?RD"
Like "*RD"
Like "[MN]RD"
"""MRD"" OR ""NRD"""

Neither work in the IIf statement because they apparently don't accept wildcards, so it looks for "[asterisk]RD" instead of "[Any Character]RD".

There's probably another way to do this, but at this point I'm annoyed that I can't figure out how to use wildcards in the truepart or falsepart statements.

Google searches have been inconclusive; the best result was a link to ExpertsExchange, which is a members-only site.

posted by JeremiahBritt to computers & internet (9 comments total)

There may be another approach you could use. Perhaps something like:

RIGHT([FieldNameHere],2) = RIGHT([Field2NameHere],2)

This would be true if the last 2 characters are the same in each each expression.

Also, if you get to an sexpertsexchange page directly from a google link, scroll all the way down to the bottom of the page to see the answers
posted by Zetetics at 7:57 AM on February 11

Do you still have a link to the ExpertsExchange question? If you scroll down to the bottom of the page, the "hidden" responses are all there. Not sure why they do that.
posted by junesix at 8:19 AM on February 11

*doh*
posted by junesix at 8:19 AM on February 11

This works for me in Access 2002:

IIf(cms.cl_key Like "T*","Tide","Crest")
posted by mgkk at 8:22 AM on February 11

I don't really follow what you're trying to do.

First, are you sure you want to be using the IIf statement? IIf is (clause), true condition, false condition. You've got 3 nestings of IIfs there which it looks like would translate to if the option group=1 is true, the value of the variable will be "MRD". If it's false and option group = 2 is true, the value of the variable will be "NRD". If *that's* false and the option group = 3 is true, what will the value of the variable be (this is where you want to use the wild card)? What's the value is option group = 3 is false? Maybe you actually want to use the Switch statement?

But my bigger question is why would you want to set a variable to a wild card?
posted by jasper411 at 9:47 AM on February 11

I think the wild card there is '%' or '&', and I'm sorry I forget which.
posted by A Terrible Llama at 9:52 AM on February 11

Like " & " & "%" & Param & "%" & " & ")"

??
posted by A Terrible Llama at 9:54 AM on February 11

sorry, that just ate my syntax chr 34 is ascii for the quote mark
posted by A Terrible Llama at 9:55 AM on February 11

Zetetics and junesix:
I did not know that the site does that. That is ridiculous. I wonder if, when you pay for membership, they just send you an email saying "Google, then scroll down". If you cancel membership, "Don't scroll".

I found the answer there; basically I need to put the Like outside of the IIf statement:

Like IIf([Forms]![Simple Selector]![OptGrpMemType]=3,"?RD",IIf([Forms]![Simple Selector]![OptGrpMemType]=1,"MRD","NRD"))

Which sounds very Silicon Valley Girl to me.
posted by JeremiahBritt at 10:24 AM on February 11