Clean up ugly data in a2k table

[Ernie]

I have a large access2k table in which users have entered
data such as names/addresses and phone numbers with no
restrictions or verification on what they entered. As a
result, I get records with data similar to this:

Mr. Smith, John; 435 Some St.; 989 555 12 12



Notice the excess spaces used. Is there a quick and easy
way to clean up these fields, without having to pull each
one up and re-type it? I know that I can use "Trim()" to
remove leading and trailing spaces, but that doesn't help
with internal spacing. Also using "format" for the phone
number doesn't get rid of the space in '12 12'.

Any ideas?

 

[Norman Yuan]

Try Replace() function: strMyRecordValye=Replace(strMyRecordValue, " ", "
"). Here you replace two space with one space. If there is posibility for 3
or 4 spaces, you can simply call Replace several times:

strMyRecordValye=Replace(strMyRecordValue, " ", " ") 'replace 4
spaces with 1 space
strMyRecordValye=Replace(strMyRecordValue, " ", " ") 'replace 3
spaces with 1 space
strMyRecordValye=Replace(strMyRecordValue, " ", " ") 'replace 2
spaces with 1 space

This repeated call may be not that efficient, but if you only want to to
do is to fix existing data, it is simple and does the job.

 

[Ernie]

When I use "Replace(myText, " ", " ") in an update query,
I get the message "Undefined function (Replace) in
expression.
This is in Access 2000.

The F1 - Help on "replace" only references the replace
option on the edit menu. I need to use it in a query.

-----Original Message-----
Try Replace() function: strMyRecordValye=Replace (strMyRecordValue, " ", "
"). Here you replace two space with one space. If there is posibility for 3
or 4 spaces, you can simply call Replace several times:

strMyRecordValye=Replace

(strMyRecordValue, " ", " ") 'replace 4

spaces with 1 space
strMyRecordValye=Replace

(strMyRecordValue, " ", " ") 'replace 3

spaces with 1 space
strMyRecordValye=Replace

(strMyRecordValue, " ", " ") 'replace 2

 

[John Vinson]

When I use "Replace(myText, " ", " ") in an update query,
I get the message "Undefined function (Replace) in
expression.
This is in Access 2000.

The F1 - Help on "replace" only references the replace
option on the edit menu. I need to use it in a query.

This was an annoying error in the design of 2000, fixed in 2002: the
Replace function exists but you can't use it in queries.

The solution is to write a dumb little wrapper function. In a Module
enter

Public Function qReplace(strIn As String, strOld As String, _
strNew As String) As String
qReplace = Replace(strIn, strOld, strNew)
End Function

and use qReplace instead of Replace in your query.

 

[Ernie]

I can do that ... Thank you VERY MUCH !!!

-----Original Message-----


This was an annoying error in the design of 2000, fixed in 2002: the
Replace function exists but you can't use it in queries.

The solution is to write a dumb little wrapper function. In a Module
enter

Public Function qReplace(strIn As String, strOld As String, _
strNew As String) As String
qReplace = Replace(strIn, strOld, strNew)
End Function

and use qReplace instead of Replace in your query.