Find/replace in an access table via macro/vb

[jonnyboy]

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

[FishVal]

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 ...)

[nico5038]

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:

function fncReplace(strInput as String) as String
 
' Replace the single LF (linefeed) with the DOS CRLF (CarriageReturn + LineFeed)
fncReplace = Replace(strInput,chr(10),chr(13)&chr(10))
 
end function
 

Now in an Update query use:

UPDATE tblX Set FieldText = fncReplace(FieldText)
 

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)

[jonnyboy]

Hi Nic,

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

Jon

[jonnyboy]

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!

[nico5038]

Glad I could help :-)

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

Nic;o)