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

Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
Rgonzo1971Connect With a Mentor Commented:

pls try

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

Open in new window

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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 SalvucciInformation Technology ManagerAuthor Commented:
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?
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

and if start is 1 you can omit it (optional)
Manuel FloresCommented:
Try different comparision modes;


    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

it isn't necessary since if 0 or null if will use the false part
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok thank you for your help. I appreciate it!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks Jim. Appreciate the info. I'll add it back in to my expression.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.