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:
This is how I constructed it:
OS Test: IIF(InStr(0,[jodbom].[fbomdesc],"-OS"),"OK", "NOT OK")
Microsoft AccessSQL
Last Comment
Lawrence Salvucci
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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
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")
it isn't necessary since if 0 or null if will use the false part
ASKER
ok thank you for your help. I appreciate it!
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.
<<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.
ASKER
Thanks Jim. Appreciate the info. I'll add it back in to my expression.
Open in new window