CLEAN expression for Access to remove nonprinting characters?

  • Thread starter Thread starter Rhea
  • Start date Start date
R

Rhea

I am looking for a way to remove paragraph returns from a
free-form text field in an Access table or query. I know
Excel has a function called CLEAN which will remove all
nonprinting characters from the cell, but I can't find a
similar expression for Access...any suggestions?

Thanks in advance,
Rhea
 
If you're using Access 2002 or 2003, you can use the Replace function in an
Update query:

UPDATE MyTable
SET TextField = Replace(TextField, Chr(10), " ")

UPDATE MyTable
SET TextField = Replace(TextField, Chr(13), " ")

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Rhea,

Open Access (I'm using 2000 SR-3, Jet SP-6, DAO 3.6), and hit cntl-G to
bring up the Visual Basic Editor.

Use the menus. Tools>References. Use the References Dialog Box to select
the Microsoft Excel 9.0 Object Library (Scroll down quite a ways to find
it). Close the Dialog Box.

If the Immediate Window/Pane isn't open, hit cntl-G again.

Type in len("333" & chr(13) & "4"). The answer is 5 bytes.

Type in debug.Print len(worksheetfunction.Clean("333" & chr(13) & "4")).
The answer is 4 bytes.

So, the answer to your question is, yes, it can be done.


Sincerely,

Chris O.
 
Back
Top