Using the Left function inside a "NOT LIKE" comparison

purepremiumpulp

Registered User.
Local time
Today, 18:03
Joined
Jun 23, 2006
Messages
11
Hi, I'm trying to compare 2 name fields to see if the last name of one field is found in the last name of the other field. In table A, the name is stored as LastName, FirstName. I've used the Left and InStr function to extract the last name successfully.
In Table S the name is stored as separate fields, Lastname and Firstname.

In a query I am trying to determine if the last name from table A appears in the last name field in table S

(s.lname Not Like '*Left([a.name],InStr([a.name],",")-1)*')

the problem I think I'm encountering is that I want access to see this statement as:

(s.lname NOT LIKE '*JOHNSON*') instead of searching for the Left function string

is there any way to put the value of the function in there without actually searching the function literally?

I tried [ ] and that didn't work.. any ideas? thanks!
 
Last edited:
How 'bout this condition

Code:
s.lname <> Left([a.name],InStr([a.name],",")-1)

RV
 
RV,
that expression worked great, thanks!:D :cool:
 
could someone please explain what <> actually does?
i've tried searching but can't find a good description
 
<> means "not equal". Your problem had to do with using Like rather than <>
 
that would mean that the strings have to be identical or <> will report that as (not equal = true), right?

<> worked in this situation, but I was using test data with only 50 records

I used LIKE '* *' because I wanted it to work even if the strings weren't identical

for instance, if the last name field from one table was "Johnson II" and the left function from the other table extracts only "Johnson" then I want it to still say that the fields are valid
 
Last edited:
I ended up using this

And (InStr([s.lname],(Left([hr.name],InStr([hr.name],",")-1))) = 0);

and it works the way I wanted it to... thanks for everyone's input
 

Users who are viewing this thread

Back
Top Bottom