MS Access 2003 Replace function

All,

I'm trying to remove dash characters froma specific field in all records of my database.  I only need to do this once and didn't want to have to write/learn how to create a module to do this.

UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], '', '') is giving me an error on the REPLACE function.  What am I doing wrong?

Please help!
nkirkesAsked:
Who is Participating?
 
RgGray3Commented:
If this is something that only needs to be done once...

Open the table in view mode
Place your cursor in the field
Press Ctrl - H
In find what enter a single dash
Leave the replace with blank

Click Replace all

I am all for SQL...  but the key here is "gotta do it once"

Rich
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
REPLACE([NewCustID], "-", "")

Replace(the string you want to search, the sub-string you want to find, the string you want to replace it with.)
0
 
nkirkesAuthor Commented:
sorry, should be UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], '-', '')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nkirkesAuthor Commented:
yeah, I mistyped the code, but I have what you wrote.  It fails.
0
 
nkirkesAuthor Commented:
could it be because there are more than one dash in the text string?  I thought it would replace all occurrences.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The SQL looks good, assuming that 37-PDLOANS-copy is a table, NewCustID is a text field within the table.

>It fails.
What's the error message?

>could it be because there are more than one dash in the text string?
No.  it would replace all occurances.
0
 
nkirkesAuthor Commented:
word for word:

Compile error. in query expression 'REPLACE([NewCustID], "-", "")'.

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you are feeding this SQL into a string, try using single quotes instead of double-quotes.  For example..

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

Dim sSQL as String
sSQL = "UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], '-', '')"

Otherwise, double-check the spelling of your table and field names.
0
 
nkirkesAuthor Commented:
it's just a query in ms-access itself.  I've looked at spelling and at using single vs. double quotes.  If I use:

UPDATE [37-PDLOANS-copy] SET NewCustID = <another field or string value>  it works.
0
 
Jim P.Commented:
UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], "-", "")

What is the error message you get with this?

If a compile error then check references:  Ctrl+G -->Tools--> References and see if any say MISSING
0
 
nkirkesAuthor Commented:
references are there.  see earlier posts for error message.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Wild guess)  Perhaps this is erroring out because one of the values in NewCustID is a NULL value.

sSQL = "UPDATE [37-PDLOANS-copy] SET NewCustID = iif(Not IsNull([NewCustID]), REPLACE([NewCustID], '-', '')", '')
0
 
nkirkesAuthor Commented:
no go.  it errored as well, but I also double checked for nulls and found none.
0
 
Leigh PurvisDatabase DeveloperCommented:
How are you executing the SQL statement - and from where?
Or does it just not run directly from a saved query either?
0
 
nkirkesAuthor Commented:
it's a saved query.  doesn't run.
0
 
Jim P.Commented:
In the debug window if you do ?replace("My-Text","-","") what do you get?
0
 
Leigh PurvisDatabase DeveloperCommented:
I'd try re-registering DAO (at least).

Start>Run>RegSvr32 "DAO360.dll"

or if you have to...

Start>Run>RegSvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll"
0
 
Jim P.Commented:
Leigh,

Replace is in C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
0
 
Leigh PurvisDatabase DeveloperCommented:
I know :-)
0
 
nico5038Commented:
What happens when you use:

UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], chr(45), '')
WHERE instr([NewCustID], chr(45)) > 0

Nic;o)
0
 
RgGray3Commented:
Err...  one last question

You are trying to update NewCustID

Is this the primary key to the table... are their dependencies on this field??

That would cause a SQL error

Rich
0
 
harfangCommented:
Hello,

The message:

    Compile error. in query expression 'REPLACE([NewCustID], "-", "")'.

Means that there is a _compile_ error caused by the REPLACE expression. Only your own VB code gets compiled, so that the compile error is in your code. In other words, this line _calls_ your code.

1) Switch to VB, choose "Debug / Compile <project name>".
2) In the immediate pane, type:
    ? REPLACE(
    Do arguments appear?
3) Right-click the word REPLACE and choose "View Definition"
    Do you have a public function or Sub called REPLACE?

If that fails, you would have to look for less obvious things. Do you have a module called REPLACE or is the VB Project called REPLACE? If the query is run from a table or report, does the class module define a public funtion?

Happy hunting!
(°v°)
0
 
nkirkesAuthor Commented:
Apparantly a missing semi colon in the sql string will cause this error.  Added the ; and all was well.  pain in the ass.  I'll split points for all who helped.
0
 
nkirkesAuthor Commented:
All the comments helped.  RqGray3 posted a viable alternative solution to my problem as I only need to run this once.  Thanks everyone.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.