Formula to check for text in String from one Column to the other

[fred.s...@gmail.com]

Hello - Could someone possibly let me know which formula (that can be added to a query) would provide the following information, which is intended to evaluate each record in a table containing two columns.

[Column1] contains thousands of text values, such as "Dogcatcher", some of which are Null
[Column2] contains thousands of text values, such as "Dog", some of which are also Null

I am basically trying to determine the best IF statement formula that will check to see if the text in Column 2 is found in Column 1.

So the logic would essentially produce something like this:

1. If either Column1 or Column2 is Null, Provide a Value of 3 (i.e., this is an invalid Record)

2. If Column2's value is found in Column 1, Provide a Value of 2 (i.e., Dog can be found in Dogcatcher)

3. If Column 1 and Column 2 are NOT null, and Column 2's value is NOT found in Column1, then produce a value of 1

Thanks very much for assistance with this formula, if you happen to know how to solve it!

- Fred

[Ron Weiner]

fred.s...@gmail.com pretended :

> ---
> This email has been checked for viruses by AVG.
> http://www.avg.com

If I understand what you want correctly try this:

SELECT C1, C2, IIf(IsNull([C1]) Or
IsNull([C2]),3,IIf(InStr([c1],[c2])>0,2,1)) AS Result
FROM myTable

Here is the result of that query

C1 C2 Result
=========== ========== ======
DogCatcher Dog 2
DogCatcher Fred 1
Null Null 3
Null Dog 3
DogCatcher Null 3

Rdub