Postcode fixing

[zathrus777]

I was doing this manually in excel but now need to automate it in access. A UK postcode conforms to certain rules e.g. CR76BG should be CR7 6BG or CRO 6BG should be CR0 6BG (change the O to a 0). How do I do text manipulation of this sort in a query. It will be a load of if statements but I am not sure where to start or the syntax. Would I be better using VBA or just the expression builder?

thanks in advance

 

[philcon]

zathrus,

I've had to do this in the past using another program, not access but the theory is still the same.

What you should do first is write down on paper all the different possible input and output formats for a pcode i.e.

D71XY -----------> D__7_1XY

SE94SA -----------> SE_9_4SA

SE9 4SA-----------> SE_9_4SA

Dont worry there aren't THAT many

Then you have to work out a way of identifying the input format by identifying the length of input and whether individual components are alphabetic or numeric.

Once you have your input/output format criteria established you could simply run a series of update queries which will carry out your transformations.

If you need further help, just let me know but its worth having a go.


Regards


Phil.

 

[zathrus777]

to identify wether something was alphbetic I was using the following to get rid of the invalids (in criteria in a query):

Not Like "[A-Z][A-Z][0-9][ ][0-9][A-Z][A-Z]" And Not Like "[A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]" And Not Like "[A-Z][0-9][ ][0-9][A-Z][A-Z]" And Not Like "[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]"

which works fine and I get a table of junk ones but from there e.g. say if the first character is a space I was tryingto test as follows :

Like "[(chr(32))]*" but this brings back all records rather than those with just a space.

To the main point - you mentioned an update query would I use mid$ ? I am just guessing here. I am going to have to do it by hand for the moment as I am short of time but have to get this automated.

 

[nealvenables]

Hi,

This is a SQL procedure I developed recently to ensure postcodes always have a space in the correct place - the syntax should be easy enough to convert into an Access function (I've added a few comments to help)

<code>
-- Make sure the postcode has a space in the right place
-- -----------------------------------------------------
-- Remove all spaces
SELECT @OrgPostcode = replace(@OrgPostcode,' ','')

-- Set @Space to equal the position of the first numeric character
SELECT @Space = patindex('%[0-9]%',@OrgPostcode)

-- If the next character is numeric also, add 1 to @Space
IF substring(@OrgPostcode, @Space + 2, 1) like '[0-9]'
BEGIN
SELECT @Space = @Space + 1
END
-- Insert a space at position @Space
SELECT @OrgPostcode = stuff(@OrgPostcode,@Space+1,0,' ')

</code>