updatting decimal numbers, why do I get just integers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

efandango - 04 Nov 2007 18:29 GMT

I am trying to update a field based on Lat/Lon 'decimal' numbers, but
whenever I up to another table I get just single whole digits.

This is a sample of the doner data: (tbl_School_Points)

coordinates
-0.020106,51.50740400000001,0
0.045455,51.50896500000001,0
-0.07613200000000001,51.54164200000001,0
-0.074599,51.546712,0
-0.057798,51.506394,0
-0.069962,51.542489,0
-0.061783,51.536706,0

and this is what I get in the other table: (tbal_XML_Imports)

coordinates
1
1
1
1
1
1
1

Both fields are set to 'text' amd not numbers (because of their unique
properties; eg: micing decimal points with commas

This is my Sql:

UPDATE INNER JOIN [London XML Imports] ON (tbl_School_Points.Postcode =
[London XML Imports].Postcode) AND (tbl_School_Points.Road_Street = [London
XML Imports].Road_Street) AND (tbl_School_Points.Point = [London XML
Imports].Point) SET tbl_School_Points.coordinates = [London XML
Imports].[coordinates];

Reply to this Message

John Spencer - 05 Nov 2007 02:34 GMT

Your posted SQL is incorrect. Also you say you want values in
tbl_XML_Imports, but your query is updating tbl_school_points

Should your SQL be
UPDATE Tbl_School_Points INNER JOIN [London XML Imports] ON
(tbl_School_Points.Postcode = [London XML Imports].Postcode) AND
(tbl_School_Points.Road_Street = [London XML Imports].Road_Street) AND
(tbl_School_Points.Point = [London XML Imports].Point)
SET tbl_School_Points.coordinates = [London XML Imports].[coordinates];

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> I am trying to update a field based on Lat/Lon 'decimal' numbers, but
> whenever I up to another table I get just single whole digits.
[quoted text clipped - 31 lines]
> Imports].Point) SET tbl_School_Points.coordinates = [London XML
> Imports].[coordinates];

Reply to this Message

efandango - 08 Nov 2007 21:29 GMT

John,

Yes, you are quite correct. my SQl is/was a mess, and was updating the wrong
way around. I have since got my head around updates. But can you tell me the
best format for using Gecodes, Latitude & Longtitude such as:

-0.07613200000000001 and 51.54164200000001,0

I have these figures in various excel files and they are, due to still
developing my app going through various processes which from time to time
throws the wrong format resulting in single digits and the like; so I am
looking for the best catch-all format. (i hope this makes sense)

> Your posted SQL is incorrect. Also you say you want values in
> tbl_XML_Imports, but your query is updating tbl_school_points
[quoted text clipped - 48 lines]
> > Imports].Point) SET tbl_School_Points.coordinates = [London XML
> > Imports].[coordinates];

Reply to this Message