Removing a "." Update query

  • Thread starter Thread starter AZEye
  • Start date Start date
A

AZEye

I have a text field in a table that is ten characters
long with a period located somewhere within that data.

I.E. "12345.0000 or 123.002545 or 123456.789"

Is there an update query I can run to take out that
period?

Thanks in advance
 
Open the table in Datasheet view and Use Find/Replace ( Find . and Replace
with Nothing - be sure you have selected to do this operation only for one
field > "Search only current field")
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access 2000 and higher versions:

UPDATE table_name
SET number_column = Replace(number_column, ".", "")
WHERE number_column LIKE "*.*"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRzyHoechKqOuFEgEQJj8ACfTnCkS8hAgbfRHKoR/W+i92dsstEAn3/U
XE1zDUNQEWbBWkwCf6Rf5c8R
=JL4o
-----END PGP SIGNATURE-----
 
PS. Instead of parsing the results into separate fields as in the KB
example query, just concatenate the two halves of the number in your
Update query. This will work no matter the length of the string and no
matter where in the string the "." is.

UPDATE MyTable SET MyTable.MyNumber =
IIf(InStr(1,[MyNumber],".")>0,Left$([MyNumber],InStr(1,[MyNumber],".")-1)
&
Right$([MyNumber],Len([MyNumber])-InStr(1,[MyNumber],".")-1),[MyNumber]);

LeAnne
 
Back
Top