Solved

MS Access 2007 Replace Macro

Posted on 2008-10-30
7
229 Views
Last Modified: 2013-11-27
How i replace a specific value into a hole table using a Macro.    For Example if you have a field called Name and you need change every quoutes by a dash -.
0
Comment
Question by:JAYAYO
  • 3
  • 2
  • 2
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22844382
UPDATE SomeTable
SET SomeColumn = Replace(SomeColumn, """", "-")
0
 
LVL 2

Accepted Solution

by:
BobTheViolent earned 250 total points
ID: 22844417
I suggest using an SQL query.

In the macro you would do a "DoCmd.RunSQL" and then this.

UPDATE TABLENAME SET TABLENAME.FIELD= Replace([FIELD],Chr(34),"-");

this example replaces all occurences of a double quote with a dash as you asked.


Look up a chr code on this website to know which chr(#) to use
http://www.asciitable.com/
0
 

Author Comment

by:JAYAYO
ID: 22847252
Fastest response Excellent you save many work hours.
Thanks
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Closing Comment

by:JAYAYO
ID: 31511815
Class A.  Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22849101
JAYAYO,

Please explain your disposition to this question.  The syntax I provided was syntactically identical to the SQL
BobTheViolent posted, and I posted a little bit ahead of him.

A split would have suited me just fine, but I feel it is patently unfair to have received no credit on this one.

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22849102
BobTheViolent,

Please do not take my message above as a criticism.  I do not think you did anything wrong here :)

Regards,

Patrick
0
 
LVL 2

Expert Comment

by:BobTheViolent
ID: 22851327
matthewspatrick,
  I agree with you.  A split 50/50 would be fair since you did post before me with the same basic answer and we were both correct.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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