Hi Bob,
At your suggestion, I've taken a look at Allen's ParseWord()
function. One thing about it gives me pause for thought. Here
is my take on it. If anyone can throw any light on the subject,
I'd be glad to hear.
As you know, Allen's ParseWord() function is a generic solution
and, as such, is not a complete solution to Vi's problem, though
it could be part of a solution.
The main issue I have with Allen's function is his use of the
Nz() function with the vbNullString constant early on and his use
of vbNullString in a comparison expression later. He doesn't use
the IsNull() function, which I think would be preferable.
In testing for vbNullString, Allen appears to equate it with a
zero-length string. He says in his comment towards the end:
"Return the result, or a null if it is a zero-length string". He
follows this with "If strResult <> vbNullString Then".
Access help says (about vbNullString): "Not the same as a
zero-length string (""); used for calling external procedures".
My understanding is that the vbNullString constant places memory
address zero in a BStr. (BStr is the type of string used in VBA.)
Memory address zero indicates to VBA that the BStr doesn't point
to a string of characters in memory (not even a zero-length
string). Memory address zero has special significance to VBA
when calling external functions, like Windows API's.
When an external function is declared in VBA, it might have a
parameter declared "As String", when, in fact, the function will
accept a string or Null in the parameter. If you then pass
Null directly to the string parameter, VB's type-checking will
generate a run-time error. In contrast, if you pass
vbNullString, VBA will place memory address zero in the BStr (the
string variable acting as the parameter) and will, as a
consequence, pass Null to the external function. Significantly,
VBA's type-checking will be by-passed so that Null can be sent to
the function despite the parameter being declared "As String".
If a BStr contains a memory address greater than zero, it points
to a string of characters in memory, which might be of
zero-length. For example, a non-zero memory address is placed in
a BStr that points to a zero-length string when you write:
strMyString = "".
Allen compares strResult with vbNullString at the end of his
function ("If strResult <> vbNullString Then"). It seems the
vbNullString constant wasn't intended for use in comparison
expressions. It would be interesting to discover what, exactly,
is being compared in such an expression. As mentioned, I would
have used the IsNull() function at the beginning and re-coded
accordingly.
Regards,
Geoff
raskew via AccessMonster.com said:
Hi -
You might take at Allen Browne's code here:
http://allenbrowne.com/func-10.html
Bob
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
In a Table Named 'Email' and a Filed in that same table named
'Email', pop in
[quoted text clipped - 38 lines]
Thanks in advance for any assistance
Vi[/QUOTE]
[/QUOTE]
