By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

Delete a space in a string depending on the fields lenght

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
"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)

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.