remove space and chars. replace() does not work

  • Thread starter Thread starter removeChars
  • Start date Start date
R

removeChars

I need to remove characters and spaces from a database field and
replace() is not working for me. I dont know if I am using it
properly. I am putting it in the queries window and it says I cannot
use it without select or update. Can some1 help me on how exactly to
use replace().

Thank you in advance.
 
removeChars said:
I need to remove characters and spaces from a database field and
replace() is not working for me. I dont know if I am using it
properly. I am putting it in the queries window and it says I cannot
use it without select or update. Can some1 help me on how exactly to
use replace().
Click to expand...


Replace did not exist before A2K and was not integrated with
queries until later.

Beyond that, what is the exact SQL view of your query?
 
I need to remove characters and spaces from a database field and
replace() is not working for me. I dont know if I am using it
properly. I am putting it in the queries window and it says I cannot
use it without select or update. Can some1 help me on how exactly to
use replace().

Thank you in advance.
Click to expand...

Create a Query based on your table.

You don't say what field you want to update, so I'll call it MyField.

Select MyField into the Query.

Turn the Query into an Update query (using the Query Type or the Query menu
option).

A new line "Update To" will appear on the grid.

Type

Replace([MyField], " ", "")

in the Update To line underneath MyField (in order to replace blanks with
empty strings, thus converting "Text Like This" to "TextLikeThis").

Run the query by clicking the ! icon.



If you want to replace blanks, hyphens, periods and commas with null strings,
use

Replace(Replace(Replace(Replace([MyField], " ", ""), "-", ""), ".", ""), ",",
"")

that is - nest the Replace function so that each "outer" Replace works on the
results of the inner Replace.

John W. Vinson [MVP]
 
Back
Top