Taking out the Apostrophe

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

How can I get OSU for O'Sullivan instead of O'S

=UCase(LeftName(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
 
Hi Bob

Never done this so may be wrong but it were me I would use a true/false if.

Something like
IIf ( [TableName]![FieldName] Like "*'*") then
do something here

to test for the ' as some names
Mc'Sulivan
M'Sulivan
Mac'Sulivan
MacSulivan
can cause a lot of problems.

As there are so many possiblities it may be worth spending a some bit of
time of creating public modual to handle this and then call it as you need it
as - if you're going to do it you may as well do it right and cover other
names

John Smith (JS)
John Smith-Brown (JSB)
John M'Smith (JMS)
John Mac'Smith-Bown (JMSB)
etc
etc
 
Bob,

Here is a public function that you can use. I tested it against your
example and every example that Wayne provided and it worked. You should test
it with other examples.

Public Function First3Chars(OrigString As String)
Dim strMyString As String
Dim varStrLoc As Variant
Dim varAddlLen As Variant
If InStr(1, OrigString, "'") Then
varStrLoc = InStr(1, OrigString, "'")
strMyString = Left(OrigString, varStrLoc - 1)
If Len(strMyString) < 3 Then
varAddlLen = 3 - Len(strMyString)
strMyString = UCase(strMyString & Mid(OrigString, varStrLoc + 1,
varAddlLen))
Else
strMyString = UCase(strMyString)
End If
Else
strMyString = UCase(Left(OrigString, 3))
End If
First3Chars = strMyString
End Function

Watch for line wraping.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
You could try an expression like
=UCase
(Left
(Replace
(DLookup
("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID] & )& "","'",""),3))

Of course, you still have a problem with a name like
Mc Owens
since there is a space in the first three characters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Bob said:
How can I get OSU for O'Sullivan instead of O'S

=UCase(LeftName(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Click to expand...

What is LeftName? A user-defined function?

I think you may find the Replace() function useful. You can replace an
apostrophe with nothing ... essentially just discard the apostrophe
before you do that other stuff.

Here is an example I ran in the Immediate Window to get you started:

? Replace("O'Sullivan", "'", "")
OSullivan
 
Bob,

You can use the replace() function to remove the single quote from
the last name.

Try this: UCase(Left(Replace([OwnerLastName], "'", ""), 3))

If the ownerlast name is O'Sullivan the code will return OSU
 
Thanks John but got the error "Too Many Paramenters"
Regards Bob
John Spencer said:
You could try an expression like
=UCase
(Left
(Replace
(DLookup
("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID] & )&
"","'",""),3))

Of course, you still have a problem with a name like
Mc Owens
since there is a space in the first three characters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob said:
How can I get OSU for O'Sullivan instead of O'S

=UCase(LeftName(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] =
" & [tbOwnerID] & ""),""),3))
Click to expand...
Click to expand...
 
Back
Top