Remove Space

[SteveE]

I have an enormous Post Code Table 1.7 million records, which I must update every month into usable sectors for our carrier cross refs, the UK postcode is in the format CB6 3HQ, CB6 3HR, CB6 3HS, CB6 3HT, CB6 3HW etc I need to be able to remove the space then only use the first 4 chrs ie CB63, in the above examples I know I could do a search and replace " " with "" but I need this to be run automaticly where would be the best place to do this and if coded how?

Any help or alternative suggetions appriciated

 

[raskew]

Try a search on 'onespace' and see if any of those threads might help.

Bob

 

[Sergeant]

In an update query (update to)...
NewField: Left(PostCode,3) & Mid(PostCode,5,1)
In a calculated field...
=Left(PostCode,3) & Mid(PostCode,5,1)

 

[SteveE]

Many thanks for your reply, I have tried your suggestion but to no avail all I get is the first 3 chr of the [postalcode] into me new field:
TempPCS: Left([PostalCode],3) & Mid([PostalCode],5,1) am I misunderstanding the use of your suggestion?

regards

 

[namliam]

The suggested solution should return your first four characters...

?left("CB6 3HQ",3) & mid("CB6 3HQ",5,1)
CB63

Greetz

 

[Sergeant]

Just a thought, try this with some of your data...

Left(Trim([PostalCode]),3) & Mid(Trim([PostalCode]),5,1)

...This will ensure you don't have extra spaces surrounding your data.

 

[SteveE]

Thanks again for all the help but still not working: Ive been searching and came across the following code to remove spaces:

Public Function RemoveSpace(ByVal strData As String) As String
Dim strText As String
Dim intCounter As Integer
For intCounter = 1 To Len(strData)
If Mid(strData, intCounter, 1) <> " " Then
strText = strText & Mid(strData, intCounter, 1)
End If
Next intCounter
RemoveSpace = strText
End Function

This looks like its just what I am after, I have created an update query as advised putting, RemoveSpace([PostalCode]) in the update but I get "Undefined Function" when trying to run it, can not see any missing references etc any advise?