Altering data within columns

[Mark909]

I have received some data with postcode information.

However there has been an error with data entry so half the postcode
information had a space between the first 3 alphanumerics and the other half
dont have a space.

i.e half have been entered as NH116BZ

the other half have been entered as NH12 8NZ

Is there a way I can alter the text in the column so that all the data will
be displayed with a space in the centre?

 

[Douglas J. Steele]

Assuming you want this change made permanently, you could use an Update
query along the lines of:

UPDATE MyTable
SET MyField = Left([MyField], 3) & " " & Mid([MyField], 4)
WHERE Mid([MyField], 4) <> " "

 

[Mark909]

Thanks for that. However im completely new to access and am struggling with
the query.

Any chance of a step by step walk through??

Assuming you want this change made permanently, you could use an Update
query along the lines of:

UPDATE MyTable
SET MyField = Left([MyField], 3) & " " & Mid([MyField], 4)
WHERE Mid([MyField], 4) <> " "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have received some data with postcode information.

However there has been an error with data entry so half the postcode
information had a space between the first 3 alphanumerics and the other
half
dont have a space.

i.e half have been entered as NH116BZ

the other half have been entered as NH12 8NZ

Is there a way I can alter the text in the column so that all the data
will
be displayed with a space in the centre?