where clause - using mid and instr functions

[chuck_kruelle]

need some help with the following select stmt:

SELECT CIEmpDir.EmailAddress, CIEmpDir.WireTransferLimitCust
FROM CIEmpDir
WHERE (( (Mid(([CIEmpDir].[EmailAddress]), 1, (InStr(1, ([CIEmpDir].
[EmailAddress]), "@")) )) = "bobsmith"));

whats wrong?

i need to add "-1" to the instr function. i'm trying to extract the
name from email address.

any help would be appreciated
chuckcycles

 

[KARL DEWEY]

I missed somthing.
If you have e-mail (e-mail address removed) then what do you want to extract?

 

[John Spencer]

Why not just use the a like opeartor in the where clause

SELECT CIEmpDir.EmailAddress
, CIEmpDir.WireTransferLimitCust
FROM CIEmpDir
WHERE EmailAddress Like "bobsmith@*"

Of course, you could use something like the following, but it would not be
nearly as efficient.

LEFT([EmailAddress], Instr(1,[EmailAddress] & "@","@") -1 ) = "bobsmith"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..