Delete a space in a string depending on the fields lenght

[panorris]

I am an infrequent user of Access and have a problem which I hope
someone may be able to help me with. I have a text field in a database
which contains UK postcodes similar to EH4 7TY or DN10 9IU. As shown
in the two examples, a postcode can either have seven characters
including the space in the middle or 8 characters including the space
in the middle. What I would like to do if delete the space from those
postcodes which are 8 characters long while leaving the space in the
shorter postcodes. Hence all the postcodes will then consist of seven
characters. The postcode EH4 7TY should stay as it is with a space
while the postcode DN10 9IU should become DN109IU. Any advice on how
to do this would be appriciated.

Many thanks in advance,

Paul

[Terry Kreft]

1) Create a backup of the table
2) Create a new query (cancel the select dialog that appears)
3) Switch to SQL view
4) Paste the following SQl in
UPDATE tblPostCodes
SET post_code = IIf(Len([post_code])>7,Left([post_code],InStr([post_code],"
")-1) & Mid([post_code],InStr([post_code]," ")+1),[post_code]);

5) Edit the SQL above replacing the table name and field name with the
correct names from your database.
6) Run the query.
--
Terry Kreft
MVP Microsoft Access
"panorris" <p.********@sms.ed.ac.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I am an infrequent user of Access and have a problem which I hope
someone may be able to help me with. I have a text field in a database
which contains UK postcodes similar to EH4 7TY or DN10 9IU. As shown
in the two examples, a postcode can either have seven characters
including the space in the middle or 8 characters including the space
in the middle. What I would like to do if delete the space from those
postcodes which are 8 characters long while leaving the space in the
shorter postcodes. Hence all the postcodes will then consist of seven
characters. The postcode EH4 7TY should stay as it is with a space
while the postcode DN10 9IU should become DN109IU. Any advice on how
to do this would be appriciated.

Many thanks in advance,

Paul

[Justin Hoffman]

"panorris" <p.********@sms.ed.ac.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I am an infrequent user of Access and have a problem which I hope
someone may be able to help me with. I have a text field in a database
which contains UK postcodes similar to EH4 7TY or DN10 9IU. As shown
in the two examples, a postcode can either have seven characters
including the space in the middle or 8 characters including the space
in the middle. What I would like to do if delete the space from those
postcodes which are 8 characters long while leaving the space in the
shorter postcodes. Hence all the postcodes will then consist of seven
characters. The postcode EH4 7TY should stay as it is with a space
while the postcode DN10 9IU should become DN109IU. Any advice on how
to do this would be appriciated.

Many thanks in advance,

Paul

What about postcodes like "N1 9JN"? Will you try to add two spaces to make
it seven characters long? I don't really see the point in doing this, but
if you know how to do update queries, you could remove all spaces from all
postcodes using Replace(MyField, " ", "") and then put the space back in
using the left and right functions, e.g.

NewPostcode:Left([MyField],Len([MyField])-3) & " " & Right([MyField],3)