Delete Spaces in text field

[nickwass]

We have a table with approx 2m records. Just three fields:
Key (primary)
Postcode
Postcode reference

The problem is with the postcodes. Many of these have a double space in them such as this DE13 7EL but we need DE13 7EL.

Find and Replace runs into a problem because of the number of records.

An update query seems obvious but we can't figure out what to put in Update to as this appears to return the literal value.
We are not VBA programmers so that isn't really an option for us.
How can we strip out that extra space?

 

[workmad3]

Query 1:

"SELECT Key, Postcode FROM table WHERE Postcode LIKE '*  *'"

get that into a recordset in VBA, then do

Dim rs as DAO.RecordSet
Set rs = CurrentDB.OpenRecordset("SELECT Key, Postcode FROM table WHERE Postcode LIKE '*  *'")
With rs
While not .EOF    
   CurrentDB.Execute "UPDATE table SET Postcode = '" & Replace(.Field("PostCode"), " ", "  ") & "' WHERE Key = " & .Field("Key")
   .movenext
Wend
End with

That should find and replace all the double spaces with single spaces. But I'm not 100% certain on the syntax

 

[RV]

Replace function should read

Replace(.Field("PostCode"), " ", " ")

as you want to strip out a space, not add one

RV

 

[workmad3]

Oh, good point

Oh, btw if you don't put things with multiple spaces in

 tags, it strips out the extra space. So my replace should be
[code]
Replace(.Field("PostCode"), "  ", " ")

 

[nickwass]

Into a recordset

OK. You are aiding the completey ignorant here.

I understand what the VBA bit is doing. I also understand the qury bit. How do I get it "into a recordset" ?

 

[workmad3]

Oops... thats what I did with the CurrentDB.OpenRecordset bit... ignore the commet Basically I meant getting a recordset type variable populated with the result of the query

 

[Brianwarnock]

A maths master told us "A little wit can save a lot of sweat" but just occassionally one has to take the opposite view. If you really cannot handle the VBA then maybe you should split up the table into say 10 new tables using create table queries selecting by using < "E" then > "E" AND < "G" etc then do the Replace on those, Then Run append queries to ceate a new table. A lot of work but simple.
Just a suggestion.

Brian