If the string contains a text Value

[naungsai]

I have a column containing Text strings. (Column1 is text field)

Column1
123
145
235
369
456

In this Column1, I want to know if the record contained "1" OR "2", the Column2 will give the result "Yes". If not, "No".

Column1 Column2
123 Yes
145 Yes
235 Yes
369 No
456 No

Please help me. I am not sure whether to post this question in Query or Module. Both Expression or function are OK for me.

 

[pbaldy]

Look in Help at InStr(), which you can use to test for those characters (either in an expression or in code).

 

[naungsai]

Look in Help at InStr(), which you can use to test for those characters (either in an expression or in code).

Dear pbaldy

I think I've got one expression.

Column2: IIf((InStrRev(nz([Column1]),"1") Or InStrRev(nz([Column1]),"2"))=0,"Yes","No")

But if you have more decent one, I still one to learn.

Best

 

[raskew]

Hi -

The InStr() function returns the position of specified character(s) in a string, e.g.

x = "abcde"
? instr(x, "b")
2
-or-
x = "135"
? instr(x, 3)
2
-or-
x = "135"
? instr(x, 2)
0

The InStrRev() function does the same thing but starts at the far right and works right to left. It's useful if attempting to find the last occurence of a character in a string.

In your example, it serves no purpose since you're looking for a "1" or "2" anywhere in the string. Additionally, the posted logic will return "Yes" if the string does not contain 1 or 2, or "No" if it does. Here's how you might check for "1" or "2" in a string using the InStr() function.

x = "135"
? iif(instr(x,1) & instr(x,2)>0, "Yes", "No")
Yes

x = "357"
? iif(instr(x,1) & instr(x,2)>0, "Yes", "No")
No

To further understand, lookup the InStr() function in the help file.

HTH - Bob

 

[naungsai]

Dear Bob

I actually do not notice the InStrRev works in reverse text direction. Thank you for your clarification. I have replaced the InStrRev wtih InStr.

thank you.

 

[raskew]

Hi -

Note that just replacing InStrRev() with InStr() is not going to, by itself, resolve the problem. Checkout my examples and remarks regarding logic.

Bob