How do I now add an extra space to Access field in Customer dbase

[Guest]

I have one space separating the two elements of the UK postcode in my
customer postcode field in Access. How can I add an extra space so there are
two spaces between the elements in order to qualify for Royal Mail discount?
THANK YOU VERY MUCH

 

[tina]

in a query, create a calculated field, as

FixedPost: Left([PostCodeFieldName], n) & " " & Right([PostCodeFieldName, n)

replace the left "n" with the number of characters in the first element AND
the space; replace the right "n" with the number of characters in the second
element. for example, for a postcode of "12345 678", the first n = 6 and the
second n = 3. also replace "PostCodeFieldName" with the correct fieldname,
of course.

hth

 

[John Spencer]

Assumption
The length of characters in the sections of postcode is not a constant. For
instance, Ab xxx and Abxx cc could be valid Postcodes.

The following should handle these cases for postcodes.
-- Postcode has no spaces
-- Postcode has one space
-- Postcode is blank (null)
-- Postcode has two or more consecutive spaces
It won't fix those cases where there are multiple instances of spaces in the
post code

FixedPostCode: IIF([PostCode] Like "* *",
TRIM( Left([PostCode],Instr(1,[PostCode]," "))) & " " &
Trim(Mid([PostCode],Instr(1,[PostCode]," ")))
, [PostCode] )

If the first section of postcode is always a specific size, the above code
is much more complex than needed.