Formatting output of a query - removing white space

[Guest]

HI,

I have a query in an Access database that returns Postal Code. Now the data
that has been imported isn't quite clean enough for me, the Postal Code is
actually varying from 6-7 chars(space in the middle for approx. half of the
data)

How can I either, format the output of the data in my query, or use an
update query to fix the data so that there is NO WHITE SPACE in the Postal
Code string?

A1B 2C3 ----> A1B2C3

I can select out the group I need to change by using "WHERE [Postal Code]
like "* *" if I want to change it with an update query, but I don't know how
to say "take the first 3 chars, and the last 3 chars, and make a string from
that..."

Any help is much appreciated.

 

[Guest]

Using the Trim function should work. Ex. Trim(ZipCode). ZipCode being the
field containing the data you want to remove.

 

[SirPoonga]

No, Trim only takes whitespace from the ends of strings, not from the
middle.

Try a Replace([Postal Code], " ", "")

 

[Joseph Meehan]

HI,

I have a query in an Access database that returns Postal Code. Now
the data that has been imported isn't quite clean enough for me, the
Postal Code is actually varying from 6-7 chars(space in the middle
for approx. half of the data)

How can I either, format the output of the data in my query, or use an
update query to fix the data so that there is NO WHITE SPACE in the
Postal Code string?

A1B 2C3 ----> A1B2C3

I can select out the group I need to change by using "WHERE [Postal
Code] like "* *" if I want to change it with an update query, but I
don't know how to say "take the first 3 chars, and the last 3 chars,
and make a string from that..."

Any help is much appreciated.

Does this help:

=Left([ProductName], 1) Uses the Left function to display the first
character of the value of the ProductName field.
=Right([AssetCode], 2) Uses the Right function to display the last 2
characters of the value of the AssetCode field.
=Trim([Address]) Uses the Trim function to display the value of the
Address field, removing any leading or trailing spaces.

 

[Guest]

If it is always the first 3 and last 3 you could use left([Postal Code],3) &
right([Postal Code],3). If the space is random you could use the InStr (
InStr([Postal Code]," ")function to return the position of the space in
conjunction with the right and left functions.

HI,

I have a query in an Access database that returns Postal Code. Now
the data that has been imported isn't quite clean enough for me, the
Postal Code is actually varying from 6-7 chars(space in the middle
for approx. half of the data)

How can I either, format the output of the data in my query, or use an
update query to fix the data so that there is NO WHITE SPACE in the
Postal Code string?

A1B 2C3 ----> A1B2C3

I can select out the group I need to change by using "WHERE [Postal
Code] like "* *" if I want to change it with an update query, but I
don't know how to say "take the first 3 chars, and the last 3 chars,
and make a string from that..."

Any help is much appreciated.