Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access 2003 Replace function

Posted on 2006-05-05
25
Medium Priority
?
497 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:nkirkes
  • 10
  • 4
  • 4
  • +4
25 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 336 total points
ID: 16615816
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
 

Author Comment

by:nkirkes
ID: 16615819
sorry, should be UPDATE [37-PDLOANS-copy] SET NewCustID = REPLACE([NewCustID], '-', '')
0
 

Author Comment

by:nkirkes
ID: 16615830
yeah, I mistyped the code, but I have what you wrote.  It fails.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:nkirkes
ID: 16615839
could it be because there are more than one dash in the text string?  I thought it would replace all occurrences.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16615860
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
 

Author Comment

by:nkirkes
ID: 16615894
word for word:

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

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16615939
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
 

Author Comment

by:nkirkes
ID: 16615962
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 332 total points
ID: 16616047
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
 

Author Comment

by:nkirkes
ID: 16616077
references are there.  see earlier posts for error message.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16616132
(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
 

Author Comment

by:nkirkes
ID: 16616199
no go.  it errored as well, but I also double checked for nulls and found none.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 332 total points
ID: 16616269
How are you executing the SQL statement - and from where?
Or does it just not run directly from a saved query either?
0
 

Author Comment

by:nkirkes
ID: 16616282
it's a saved query.  doesn't run.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16616288
In the debug window if you do ?replace("My-Text","-","") what do you get?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16616315
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 16616351
Leigh,

Replace is in C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16616363
I know :-)
0
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 332 total points
ID: 16617385
What happens when you use:

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

Nic;o)
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 336 total points
ID: 16617876
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
 
LVL 11

Expert Comment

by:RgGray3
ID: 16617890
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 332 total points
ID: 16618148
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
 

Author Comment

by:nkirkes
ID: 16631455
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
 

Author Comment

by:nkirkes
ID: 16631496
All the comments helped.  RqGray3 posted a viable alternative solution to my problem as I only need to run this once.  Thanks everyone.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16649471
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question