Hi Ryan/Vi,
I know different people do things differently.
In my experience, I've found that writing the logic out in a
Select statement can be difficult to understand and maintain. As
my contribution to this thread, here's how I'd tackle the
problem.
In summary, I'd write a public function in a standard module.
This function would do all the hard work of splitting the email
address. I'd call the function from a query. This would
create temporary fields in the query, for the FirstName,
MiddleInitials and LastName. I've found this technique is
usually easier to understand and maintain. You can set out and
document the logic more easily. A VBA function also gives better
flexibility and control over the final outcome. For example,
I've jazzed up the function by capitalizing the first letters of
the names.
The specifics are as follows:
1. First, I'd write out the logic for splitting the email
address in the public function, SplitEmailAddress().
I've chosen not to use the Split() function.
(Vi - You can copy and paste the following code into a new, blank
standard module.)
' CODE BEGINS:
' An enumeration of long integer constants
' that determine what the SplitEmailAddress()
' function returns:
Public Enum NamePart
lngcFirstName = 1
lngcMiddleInitials = 2
lngcLastName = 3
End Enum
Public Function SplitEmailAddress( _
vntFieldValue As Variant, _
ReturnName As NamePart) As String
' IN:
' This function needs to be passed the
' email address in the vntFieldValue
' parameter (above).
'
' The incoming ReturnName parameter (above)
' determines whether this function returns
' the first name, middle initials or
' last name.
'
' OUT:
' This function returns the first name,
' middle initials or the last name that's
' in the email address, depending on whether
' the incoming ReturnName parameter
' is 1 or 2 or 3.
Dim strRetVal As String
Dim lngPos As Long
Dim lngPos1 As Long
Dim lngPos2 As Long
Dim lngPos3 As Long
Dim strNamePart As String
Dim strFirstName As String
Dim strMiddleInitials As String
Dim strLastName As String
' See if a non-string field was passed in:
If VarType(vntFieldValue) <> vbString Then
GoTo Exit_SplitEmailAddress
End If
' See if field is empty:
If IsNull(vntFieldValue) Then
GoTo Exit_SplitEmailAddress
End If
' See if field contains an @ sign:
lngPos = InStr(vntFieldValue, "@")
If lngPos = 0 Then
' Email address is invalid.
GoTo Exit_SplitEmailAddress
End If
' Get the name part of the email address:
strNamePart = Trim(Left(vntFieldValue, lngPos - 1))
' See if strNamePart contains a period:
lngPos1 = InStr(strNamePart, ".")
If lngPos1 = 0 Then
' If we're here, there's no period.
' Therefore, assume whole of strNamePart is
' last name:
strLastName = strNamePart
GoTo FixNames
End If
' See if name part contains a second period:
lngPos2 = InStr(lngPos1 + 1, strNamePart, ".")
If lngPos2 = 0 Then
' If we're here, there's only one period.
' Therefore, assume first name precedes
' the period and last name follows the
' period:
strFirstName = Left(strNamePart, lngPos1 - 1)
strLastName = Mid(strNamePart, lngPos1 + 1)
GoTo FixNames
End If
' If we're here, there's a second period.
' Search from end of string in case there
' are more than two periods:
lngPos3 = InStrRev(strNamePart, ".")
' Assume first name is before first period:
strFirstName = Trim(Left(strNamePart, lngPos1 - 1))
' Assume middle initials are between the
' first and last periods:
strMiddleInitials = Trim(Mid(strNamePart, _
lngPos1 + 1, lngPos3 - lngPos1 - 1))
' Assume last name is after last period:
strLastName = Trim(Mid(strNamePart, lngPos3 + 1))
FixNames:
' Capitalize first letter of first name:
strFirstName = UCase(Left(strFirstName, 1)) _
& Mid(strFirstName, 2)
' Capitalize the middle initials:
strMiddleInitials = UCase(strMiddleInitials)
' Capitalize first letter of last name:
strLastName = UCase(Left(strLastName, 1)) _
& Mid(strLastName, 2)
' Initialise this function's return value
' depending on the incoming ReturnName value:
Select Case ReturnName
Case lngcFirstName
strRetVal = strFirstName
Case lngcMiddleInitials
strRetVal = strMiddleInitials
Case lngcLastName
strRetVal = strLastName
Case Else
' Return nothing.
End Select
Exit_SplitEmailAddress:
' Set this function's return value:
SplitEmailAddress = strRetVal
Exit Function
End Function
' CODE ENDS.
2. Secondly, I'd write a query using the following SELECT
statement. This calls the above function, SplitEmailAddress().
(Vi - you can copy and paste the following statement by opening a
new blank query in SQL view.)
SELECT Table1.*, SplitEmailAddress(
,1) AS FirstName,
SplitEmailAddress([Email],2) AS MiddleInitials,
SplitEmailAddress([Email],3) AS LastName
FROM Table1;
In the above Select statement:
* Table1 is the name of the table.
* [Email] is the name of the field containing the email address.
* SplitEmailAddress is the name of the public function in a
standard module.
* FirstName, MiddleInitials and LastName are new temporary fields
created by the query.
Regards
Geoff
message
[QUOTE]
In a Table Named 'Email' and a Filed in that same table named
'Email', pop in
this SQL:
SELECT Email.Email, Left([Email],InStr([Email],".")-1) AS
FirstName,
IIf(Mid(Mid([Email],1),InStr(1,Mid([Email],1),".")+2,1)=".",Mid(Mid([Email],1),InStr(1,Mid([Email],1),".")+1,1),"")
AS MiddleInitial, Left([Email],InStr([Email],"@")-1) AS Expr1,
IIf(Mid(Mid([Expr1],1),InStr(1,Mid([Expr1],1),".")+2,1)=".",Mid(Mid([Expr1],1),InStr(1,Mid([Expr1],1),".")+3),Mid(Mid([Expr1],1),InStr(1,Mid([Expr1],1),".")+1))
AS LastName
FROM Email;
Change names to suit your needs.
Hide Column Expr1!
Good luck,
Ryan---
[QUOTE="Jeff Boyce"]
Vi
Oh yes, depending on the version of Access you are using, the
newer versions
have a Split() function that you might be able to use to parse
on "."s...
Jeff
[/QUOTE][/QUOTE]