I have a table within it I have a f

[TheRiver]

I have a table within it I have a field which contains postcodes

What I want to do it update all the postcodes within the field to have the same format. The problem is that I have no influence of controlling what actually is inputted into the field.

All i can do is 'clean' up the data in the field.

Is it possible to update the field from the following format:

B68 7RG
to
B68 7RG

I tried UPDATE [WL NonRes BackUp] SET [WL NonRes BackUp].PostCode = "*** ***"
WHERE ((([WL NonRes BackUp].PostCode) Like "*** ***&quot);

But is replaces everything with ***?

 

[MinusM]

If it is a one time job try a simple solution of Find and Replace in the Edit Menu of Ms Access.

Or Else

UPDATE [WL NonRes BackUp] SET [WL NonRes BackUp].PostCode = Left([WL NonRes BackUp].PostCode ,3) & " " & Right([WL NonRes BackUp].PostCode ,3) may also do


Best of luck

 

[DanChard]

How different can the format for a postcode be? Some examples of some of more weird postcodes might be handy. You might try something like the following to achieve what you gave in your example:

update [WL NonRes BackUp]
set postcode = left(postcode,instr(postcode,' ')-1) & ' ' & right(postcode,3)
where postcode like '* *';

Good luck.

 

[TheRiver]

there are many different types of Postcodes I get you would be surprised at the way I get them

For example

B687AG
B68 7AG


the problem I have using these updates is that is updates all the records of course, but some of the records are fine eg

ones where WS10 7AG they are fine but when I run this update query these are changed to

WS10 7A

I used the following update
update [WL NonRes BackUp]
set postcode = left(postcode,instr(postcode,' ')-1) & ' ' & right(postcode,3)
where postcode like '* *';

 

[MinusM]

I am assuming there is some logic in dividing the Postcode into 2 parts.
Is it that the last numeric value is to be the start of the second part of the post code???

Try the rest only if it is so

Have a new field in the table called Part2Start as integer. You can also have a flag field to mark that this is a corrected row (SET FLAG = TRUE) that does not have to be updated again.
In that case add a where condition ' And flag = false'

This is how it goes

Run an update statement to store the start of the second part


update [WL NonRes BackUp]
set part2start = switch (
IsNumeric(mid(postcode,1,Len(postcode))),Len(postcode),
IsNumeric(mid(postcode,1,Len(postcode)-1)),Len(postcode)-1,
IsNumeric(mid(postcode,1,Len(postcode)-2)),Len(postcode)-2,
IsNumeric(mid(postcode,1,Len(postcode)-3)),Len(postcode)-3,
IsNumeric(mid(postcode,1,Len(postcode)-4)),Len(postcode)-4)

update [WL NonRes BackUp]
set postcode = Trim(Left(postcode,part2start-1)) & ' ' & Trim(Right(postcode,Len(postcode)-(part2start-1))

Best of luck

 

[JonFer]

You can use "?" as a wildcard representing a single character in your WHERE clause. It will match a space.

You can also use "[A-Z0-9]" to indicate a single alphanumeric character in your field - this will not match a space.