Unmatched query, strip part of matching field?

[lorirobn]

Hi -
I am creating an Unmatched query, and the field in common on the two tables is email address.
However, on one table, the address is kept in full ("JohnSmith@abc.com"). On the other table, address is kept without the domain name ("JohnSmith").

Can I write my query around this? Is there a way to strip everything from the "@" on during the query?

My query now (which doesn't work, because it compares the two unequal fields) is:

SELECT Accounts.ID, Accounts.username
FROM Accounts LEFT JOIN STAFF  ON Accounts.username = STAFF.email
WHERE (((STAFF.email) Is Null));

Accounts is the table without domain name; STAFF is the table with domain name.

Thanks in advance for any help.
Lori

 

[rjoubert]

Left([Staff]!,InStr(1,[Staff]![email],"@",1)-1)

 

[PHV]

You may try this:
FROM Accounts LEFT JOIN STAFF ON STAFF.email Like Accounts.username & '@*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Thanks for your quick responses!
I tried PH's, and it worked great. And now I know how to append and also do a "wild card".
Thanks rjoubert, too - I will try this another time, just to learn some more.