Update or append query help

[susankay]

We have two tables with an account number field,
one is like 00012345-234
the other 00012345234
How do we strip the - out of the first example so that the values in both tables will be in the same format? I tried a search and replace, and also failed at append query.
All help appreciated.

 

[MichaelRed]

I'm not quite sure that I am following your problem. To 'remove' the "-" from a field/string, there are numerous soloutions. Perhaps the easiest is the Left/Right from the hyphen.

In an update query (Update to):
Left([AccountNumber], Instr([AccountNumber], "-&quot -1) & Right([AccountNumber], Instr([AccountNumber], "-&quot + 1)

Of course, this will fail if the hyphen is missing or at either end of the string, so if the recordset is 'garbled' other measures will need to be taken.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over