Solved

Find specific text in access query

Posted on 2016-08-11
9
56 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
9 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 49

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 49

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 57
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

785 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