Adding a space inbetween the postal incode and outcode of a postcode

[taboo]

I have a table of postcodes. All have a field size of 7 and need an extra space between the postal incode (e.g.E7) and the postal outcode (e.g.3LM). Examples of the formats are

E7 3LM (2 spaces)
E17 3LM (1 space)
EC1R3LM (0 spaces)

What is the formula required to add this space?

 

[AWithers]

One way is something like the following

SELECT IIf(Len([PC]![Postcode])=7,[PC]![Postcode],IIf(Len([PC]![Postcode])=6,Left([PC]![Postcode],3)+" "+Right([PC]![Postcode],3),IIf(Len([PC]![Postcode])=5,Left([PC]![Postcode],2)+" "+Right([PC]![Postcode],3)))) AS Expr1
FROM Postcodes AS PC;


Hope this helps

Andy