query to show only first part of email address

[Simon]

I have a list of email addresss ( over 1000) i would like to run a
query to only dispay the first part of the email address, any item
before the @


for example

Email address
(e-mail address removed)

i would like a query just to dispay johnsmith


Thanks

 

[Arvin Meyer [MVP]]

It;s called parsing, and here's a generic function to do it:

Function ParseIt(Strfrom As Variant, Cntr As Integer, Sep As String) As
String
Dim intPos As Integer
Dim intpos1 As Integer
Dim Intcnt As Integer

intPos = 0
For Intcnt = 0 To Cntr - 1
intpos1 = InStr(intPos + 1, Strfrom, Sep)
If intpos1 = 0 Then intpos1 = Len(Strfrom) + 1
If Intcnt <> Cntr - 1 Then intPos = intpos1
Next Intcnt

If intpos1 <> intPos Then
ParseIt = Mid(Strfrom, intPos + 1, intpos1 - intPos - 1)
Else
ParseIt = ""
End If

End Function

Use it like:

?ParseIt("(e-mail address removed)",1,"@")
S.Dickson

 

[Cheese_whiz]

Hi Simon,

Create a new query in design view. Show the table with the email address in
it. In the Field Row of the first column in the query design grid, type this:

EmailRoot:Left([tableName].[EmailAddress], (InStr([tableName][EmailAddress],
"@")-1)

All that is one line. The only space I used was after the comma after the
first [EmailAddress].

Change tableName to the name of the table with the email address in it (the
one showing at the top of the query design window). You still need to keep
the brackets, though.
Change EmailAddress to the name of the field that holds the email address
(the field in tableName). Again, keep the brackets.

That should work.

CW

 

[Jerry Whittle]

In the field of a query:

TheFirstPart: Left([EmailAddressField], InStr([EmailAddressField],"@")-1)