where clause - using mid and instr functions

C

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
 
J

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
..
 
Top