Deleting a space from mid post code

[Guest]

Please help

I am trying to do a lookup accross post codes and can not, as one data set
has a post code with no space and the other data set has a post code with a
space in the middle as below.

data set 1 AB12 1AB
data set 2 AB126AB

Is there a way of either removing the space from one or adding a space to
the other?

I unfortunately can't export to excel as the data is over 65k lines

 

[Douglas J. Steele]

Assuming you're using Access 2000 or newer, use the Replace function:

Replace([PostCode], " ", "")

 

[Arvin Meyer [MVP]]

You can run a couple of update queries: To reduce the first one:

UPDATE TableName Set Left([FieldName],4) & Right([FieldName],3) AS Expr1
FROM TableName
WHERE ((Len([FieldName])=8));

To increase the second one:

UPDATE TableName Set Left([FieldName],4) & " " & Right([FieldName],3) AS
Expr1
FROM TableName
WHERE ((Len([FieldName])=7));
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com