Removing apostrophe from text entry

[jkmccnh]

I'm using Access 2000 SR1. How do I remove apostrophe from a field, Replace()
doesn't work in this version of Access according to my research? I've tried
using: Mid(strText, intStart, 1) = " " which will replace the apostrophe with
a space (O'Brien to O Brien). I need the apostrophe replaced with nothing
(i.e. O'Brien to OBrien). If I use: Mid(strText, intStart, 1) = "" then
nothing happens, the apostrophe remains. I'm obviously missing something, but
what? The ultimate purpose of this is to import payroll info from a .CSV file
and compare that data to login info in the database. Payroll uses the
person's legal name (O'Brien) but the login data uses a shortened name
(OBrien).

 

[Tim Ferguson]

How do I remove apostrophe from a field, Replace()
doesn't work in this version of Access

const apostropheCode as integer = 39 ' I think...
dim tempString as string
dim i as integer

for i = 1 to Len(oldString)
if Asc(Mid$(oldString,i,1))=apostropheCode Then
' do nothing
else
tempString = tempString & (Mid$(oldString,i,1)
end if
next i

' return tempString


Hope that helps


Tim F

 

[Douglas J Steele]

You really should apply the missing service packs: you run the risk of
serious problems without some of the fixes that apply.

There was a problem using Replace in queries in Access 2000 SR1, but I
believe it was possible to write your own wrapper function that would then
work. Something along the lines of:

Function MyReplace( _
InputText As String, _
ChangeFrom As String, _
ChangeTo As String) As String

MyReplace = Replace(InputText, _
ChangeFrom, ChangeTo)

End Function

You could then use MyReplace instead of Replace.

 

[jkmccnh]

PERFECT!! I was definitely overcomplicating the problem. Thanks for your
help.

 

[jkmccnh]

Douglas,

Yes, I agree that the missing service packs should be applied but
unfortunately that's not my call to make (sigh). Thanks for the wrapper
function, it's much more concise and works perfectly.

TJ