By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Find/replace in an access table via macro/vb

P: 31
Hi,

I need a macro to replace all incidents of a carriage return in one field in an Access table with spaces. It's a table that gets reimported into a database repeatedly, and there are always unwanted carriage returns in the imported fields.

How can I do this within VB?

Jon
Aug 7 '07 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi,

I need a macro to replace all incidents of a carriage return in one field in an Access table with spaces. It's a table that gets reimported into a database repeatedly, and there are always unwanted carriage returns in the imported fields.

How can I do this within VB?

Jon
Hi, Jon.

All what you need is to design Update query and run it manually or from VBA code (DoCmd.OpenQuery ...)
Aug 7 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Personally I use a Replace() function.
As the older Access versions don't support this in a query, but do support userdefined functions (from the modules section), I tend to use this simple function:

Expand|Select|Wrap|Line Numbers
  1. function fncReplace(strInput as String) as String
  2.  
  3. ' Replace the single LF (linefeed) with the DOS CRLF (CarriageReturn + LineFeed)
  4. fncReplace = Replace(strInput,chr(10),chr(13)&chr(10))
  5.  
  6. end function
  7.  
Now in an Update query use:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblX Set FieldText = fncReplace(FieldText)
  2.  
Getting the idea?
Ofcource the Replace "from" and "to" parameters, being chr(10) and chr(13)&chr(10) can be replaced with any value needed.

Nic;o)
Aug 7 '07 #3

P: 31
Hi Nic,

Thanks very much for the contribution. I'll check it out and tell you how far I get!

Jon
Aug 13 '07 #4

P: 31
It works a treat. I'm learning loads about Access and VB on this forum. Thanks very much. I hope to be able to help others more in future!
Aug 13 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Glad I could help :-)

Success with your application and be sure that helping others also teaches you a lot !

Nic;o)
Aug 13 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.