Find specific text in access query

Lawrence Salvucci
Lawrence Salvucci used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

pls try

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

Open in new window

Regards
Lawrence SalvucciInformation Technology Manager

Author

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 Manager

Author

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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
and if start is 1 you can omit it (optional)
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

Top Expert 2016

Commented:
it isn't necessary since if 0 or null if will use the false part
Lawrence SalvucciInformation Technology Manager

Author

Commented:
ok thank you for your help. I appreciate it!
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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 SalvucciInformation Technology Manager

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial