Access SQL String to Numeric - not returning data after decimal point, not changing data type 1

[NWildblood]

Hi, I have an Access SQL query to convert String to Numeric, however it is returning whole integers only - not returning numbers after decimal points.

The table concerned has mileages in 000.0000 format as string, (it's data extracted from a very old database) however the SQL below is returning whole numbers only

E.g. 123.1234 (string) is updated to 23 (numeric)

The SQL is here.

UPDATE Route_Master SET Route_Master.MILEAGE_FROM = Clng ([MILEAGE_FROM]);

Another observation - after completing the query, the Data Type remains "Text", when I expected to see "Number"

Any help very gratefully received !

No hay nada como un buitre cabrón como un ojo de cristal.

 

[SkipVought]

hi,

Your update query is ONLY converting a STRING to LONG and converting that LONG back to STRING, which is how that field is defined.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

In addition to Skip's comment CLng() will return whole numbers only. If you need decimal places, you should use CDbl() or some other function.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

That's whey I stated LONG. It is an integral data type!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NWildblood]

Thanks for the replies guys, so can I use SQL to convert the actual data type, or will I need to use Function/VB ?
Thanks again

No hay nada como un buitre cabrón como un ojo de cristal.

 

[SkipVought]

I think you're gonna need to create a new table with correct field types, update that table.

Then delete the old table and rename the new table.

This is a one time event, YES?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

or maybe add a column with correct data type. Update THAT column.

Then delete the old string column once you're satisfied.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NWildblood]

That's a good plan, I had avoided doing that because it's such a huge table, but I'll go for it now - cheers Skip !

No hay nada como un buitre cabrón como un ojo de cristal.

 

[PHV]

Anyway, why not simply use the Val function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OutOfShape, did you even try to use the Val function ?

 

[NWildblood]

I did PHV, and it all worked fine earlier, thank you for that, bad day at the office yesterday !

No hay nada como un buitre cabrón como un ojo de cristal.