Remove space in Postcode

[emergencyplan]

Hello - I'm pulling my hair out over this!

I have 195,00 postcodes in a database, all have seven characters (including spaces0, however some only have six letters and numbers. e.g MK453EP(no space), LU1 2RS(one space).

I would like to either; add a space to the MK453EP type post code or remove a space from the LU1 2RS postcode. This is to prevent the user having to either enter a space or not when searching for a specific postcode.

Any ideas gratefully recieved,

Thanks,

 

[eAlchemist]

As long as the data is consistent, this is fairly easy to do. I would use this formula in an update query:
To add a space:
IIF(Instr([fieldname]," &quot=0, Left([fieldname],3) & " " & Right([fieldname],3), [fieldname])


To remove the space:
IIF(Instr([fieldname]," &quot>0, Left([fieldname],3) & Right([fieldname],3)

This will only work well with consistent data that doesn't have any other errant spaces.

Good Luck,
Chris

 

[emergencyplan]

Works brilliantly, many thanks.