how do I remove a space in a text value?

[roonie]

I have a field that contains 2 spaces in the Postal Code (i.e. A0A 0A0) and
I want to remove one space, so it would read A0A 0A0.

thanks

 

[KARL DEWEY]

Create an update query with this as the UPdate To --
Replace([YourPostalCodeField], " ", " ")

The first set of quotes contain two spaces and the second has only one space.

 

[fredg]

Anywhere in the [PostalCode] field?
=Replace([PostalCode]," "," ")

Only if the double spaces are in the 4th and 5th position?
=IIf(inStr([PostalCode]," ") = 4,Left([PostalCode],3) & Mid([PostalCode],5)

Fred