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?

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
http://I.Am/DougSteele
(no e-mails, please!)


"Mark909" <(E-Mail Removed)> wrote in message
news:BF205035-2535-4CDB-86E1-(E-Mail Removed)...
>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?

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

Any chance of a step by step walk through??



"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mark909" <(E-Mail Removed)> wrote in message
> news:BF205035-2535-4CDB-86E1-(E-Mail Removed)...
> >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?
>
>
>