asked on

Find specific text in access query

I am trying to see if there are specific characters in one of my fields in my access query. I need to find this specific string: "-OS". If I find it then I want to just put an "OK" in the new column in my query. If I don't find it then I just want to put a "NOT OK" in that column. The new column name is just called "OS Test". I tried using the InStr but that didn't seem to work. Can anyone shed some light on how I can set this up in my query to find that specific string in my query field? The "-OS" can be anywhere in the field so it's not always in the same place.

This is how I constructed it:

OS Test: IIF(InStr(0,[jodbom].[fbomdesc],"-OS"),"OK", "NOT OK")

Open in new window

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

I think I answered my own question. I had to change the starting position to 1 instead of 0. I didn't realize the starting position should have been 1 and not 0. Is that always the case when using the InStr function?

OS Test: IIf(InStr(1,[dbo_jodbom].[fbomdesc],"-OS")>0,"OK","NOT OK")

Open in new window

Lawrence Salvucci

ASKER

Do I need to use the >0 at the end of the InStr function? When I remove it I still get the same results so I'm guessing it doesn't need to be there, correct?

Rgonzo1971

and if start is 1 you can omit it (optional)

Manuel Flores

Try different comparision modes;

compare

Optional. This is the type of comparison to perform. The valid choices are:
VBA Constant       Value       Explanation
vbUseCompareOption       -1       Uses option compare
vbBinaryCompare       0       Binary comparison
vbTextCompare       1       Textual comparison
vbDatabaseCompare       2       Comparison based on your database

OS Test: IIF(InStr(0,[jodbom].[fbomdesc],"-OS", 1),"OK", "NOT OK")

Open in new window

Rgonzo1971

it isn't necessary since if 0 or null if will use the false part

Lawrence Salvucci

ASKER

ok thank you for your help. I appreciate it!

Jim Dettman (EE MVE)

Yes, it's always one.

<<Do I need to use the >0 at the end of the InStr function?>>

I would just to be clear. Keep in mind that an SQL condition is looking for a True / False.

In this case, InStr() returns a 0 if not found (which equals false), and a non-zero value if found (which would equate to a true).

While I can't see this ever breaking, it's relying on indirect behavior. As a general rule of thumb in programming, you *always* want to be as explicit as possible and leave nothing to chance.

So although not needed in this case, it would be better (and clearer in the future) if you included the check of >0

and BTW, a good way to check out expressions you want to use in a query is to open the VBA editor (alt/F11), then the debug window (Ctrl/G), and then type:

? <expression>

followed by a return. This will help you get the syntax right. Then you can insert it into the query replacing your test string with a field reference.

Jim.

Lawrence Salvucci

ASKER

Thanks Jim. Appreciate the info. I'll add it back in to my expression.