Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find specific text in access query

Posted on 2016-08-11
9
Medium Priority
?
94 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 54

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 54

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 54

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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