help!! remove the spaces!

[amy ratcliffe]

hi

i am really stuck. i have a field in a table and it has
a column of numbers some are in this format #### #####
#### #### ## or ### or #### and some are all together
################################. I need to keep the
last 4 digits and am finding this dificult with the ones
with spaces. Do you know how i can remove the spaces and
make it one whole number for me to deal with?

i hope this makes sense.

thank you very much

 

[Douglas J. Steele]

Assuming that the numbers are being stored in text fields, and you're using
Access 2000 or higher, you can write a query that uses the Replace function
to correct your numbers:

MyNewField: Replace([MyOriginalField], " ", "")

Note that older versions of Access 2000 (i.e.: ones that haven't had all of
the service packs installed) may have problems using the Replace function in
a query. To get around that, write your own function that calls the Replace
function:

Function MyReplace(OriginalString As Variant, ChangeFrom As String, ChangeTo
As String) As Variant

MyReplace = Replace(OriginalString, ChangeFrom, ChangeTo)

End Functioni

If you're using Access 97 or earlier, you'll have to write your own code to
mimic the Replace function. There's one approach in
http://www.mvps.org/access/strings/str0004.htm at "The Access Web"