updatting decimal numbers, why do I get just integers

G

Guest

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];
 
J

John Spencer

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
'====================================================
 
G

Guest

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)





John Spencer said:
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.

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];
Click to expand...
Click to expand...
 
Top