?
Solved

Find specific text in access query

Posted on 2016-08-11
9
Medium Priority
?
81 Views
Last Modified: 2016-08-11
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

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41752042
HI,

pls try

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

Open in new window

Regards
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41752043
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

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41752047
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?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752048
and if start is 1 you can omit it (optional)
0
 
LVL 5

Expert Comment

by:Manuel Flores
ID: 41752051
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

0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752053
it isn't necessary since if 0 or null if will use the false part
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41752056
ok thank you for your help. I appreciate it!
0
 
LVL 58
ID: 41752060
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.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41752066
Thanks Jim. Appreciate the info. I'll add it back in to my expression.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question