updatting decimal numbers, why do I get just integers

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


0
Utf
11/4/2007 6:29:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
748 Views

Similar Articles

[PageSpeed] 25

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


efandango wrote:
> 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];
> 
> 
0
John
11/5/2007 2:34:27 AM
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" wrote:

> 
> 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
> '====================================================
> 
> 
> efandango wrote:
> > 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];
> > 
> > 
> 
0
Utf
11/8/2007 9:29:01 PM
Reply:

Similar Artilces:

How to get every conytols
Hi Guys, I'm working on Word 2007 and creating a form input user. I'm doing this for a user that have Office 2003, so I have a compatibility mode template document. It also include VBA code. What i need to do is to read every existing text control. Theses controls are ActiveX. I always have used: For Each xControl In Controls If TypeOf xControl Is TextBox Then xControl.value="" End If Next xControl But now in Word 2007 in compatibility mode I do not know how to do it, becuase there is not "Controls" in the ActiveDocument. ...

Quotes won't update
Ever since I installed Money Deluxe 2004 on a new PC I bought in April individual stock quotes stopped updating. All my quote updates were working in Money 2003 Deluxe prior to the 2004 update and the new computer and they are still working on my old computer. ALL OTHER INFORMATION ( PROVIDERS, MONEY UPDATES, ETC.) ARE WORKING AND NEVER CEASED WORKING. Individual quotes and the portfolio totals are not being updated, even though the date stamp in the lower left hand corner shows the correct date and time of the update and the message says the updates were successful. Microsoft phone...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

how can text be set up to generate a number?
i would like to a word generate a number, i.e.> enter "apples"in one cell and excel generate "352" in a different cell for items like inventory. Basically You will need to set up a 'Lookup' table somewhere with Apples 352 In two columns. Complete this for all entries and then if you are going to enter 'Apples' on another sheet in A1 then in A2 enter =VLOOKUP(A1,Your_Lookup_Range_Address,2,FALSE) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "jwmaes" <jwmaes@discussions.microso...

Numbers Chart
I need a 10x10 chart of numbers 1-100 with the numbers vertical. I'm sure there is a quickie way to do it. thanks, CB -- C and A Bredt Top left corner of chart: =ROW(A1)+10*(COLUMN(A1)-1) Copy to a 10x10 area. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C and A Bredt" wrote: > I need a 10x10 chart of numbers 1-100 with the numbers vertical. > I'm sure there is a quickie way to do it. > thanks, CB > -- > C and A Bredt > Highlight the cells, format > Cells > Alignment and make it 90 degrees. "Luke...

Highlighting Updated Records
Hi I wonder whether someone may be able to help me please with a problem I have with a db I'm working on. I have a subform with approx 15 fields on it where on any given day can have their data amended, added or deleted. What I would like to do is to capture which records have been amended in a report within a given month. I know how to show which records have been updated by adding another field to the table which has a date stamp but my problem is as follows: I would like to actually show the individual fields which have been updated, rather than the whole record. Now I ha...

How to get userform Cancel button to invoke Exit Sub in calling macro?
I have a userform that is called from inside a sub in a module of a workbook. In order to keep most of the code inside the module, the userform only sets variables in the sub. Here is the code in the sub: 'create and initialize variables Dim Bkp,XSub as Boolean Dim BkpSfx as String Bkp = True XSub = False 'call options box BackupOptionsBox.Show 'Set main subroutine variables Bkp = BackupOptionsBox.YesOption BkpSfx = "_" & BackupOptionsBox.SfxBox.Value ...

I cannot get "Send" to show up in Toolbar
I think I'm set up for Outlook use, but there is no "Send" button. ?? Iuse Outlook all the time at work--I'm just rying to set it up at home. vincem wrote: > I think I'm set up for Outlook use, but there is no "Send" button. ?? > Iuse Outlook all the time at work--I'm just rying to set it up at home. Have you set up an email account? -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "Gordon" wrote: > vincem wrote: > > > I think I'm set up for Outlook use, but there is no &...

Pivot Table Updates Issue
My Data had a coloum containing stuff like Marketing Department Sales Department Shipping Department I built a bunch of pivot tables on this data. Then I changed the data to read: Marketing Dept Sales Dept Shipping Dept and performed a Refresh Data. Now, on the filter pulldowns on any of the Pivot tables, I see Marketing Department Sales Department Shipping Department Marketing Dept Sales Dept Shipping Dept The first three option no longer exist in any of the data, and I do not want them in the list. How to fix this problem without recreating and reformatting 38 seperate Pivot table...

Keyboard shortcut for editing links with updating
Can some one post the keyboard shortcut: When editing a link in a worksheet I don't want it update with new value when I hit Enter because it takes long to update(going over anetwork) and have many links to edit. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi there, There isn't a shortcut that I know of per se - what you can do is go to Tools-->Options on the Calculation tab change it from Automatic to Manual. This will apply to all links and calculatio...

Updating Standard Costs
I'm looking for an easy way to update all of our standard costs so that they can be used for quotational purposes. Unfortunately, the only way that it seems I'll be able to accomplish this is if I can create some sort of SQL script. Depending on what the difference is (percentage) between the current cost, and the current standard cost they want the new standard cost to be the current cost * x% (where x is going to vary). We're using the Horizons Manufacturing Suite, and I don't believe it has this capability. Anyone else been in this situation before? How did you a...

Cost Update Method
I'm using POS 2.0. The manual and the help information indicate you should be able to update costs throught the Store Settings > Options > Inventory screen. I do not have the 'Cost update method" options listed on that screen. How can I have my costs updated using the weighted average method? Go to File>Configuration>Inventory, in Store Operations Manager. You should be able to select Weighted Average as your Cost Update Method. "BikeShop" wrote: > I'm using POS 2.0. The manual and the help information indicate you should > be able to up...

How do i get into Publisher in Microsoft XP (new computer, sorry)
Just got a fancy new computer and am used to using Microsoft 2000. Often use Publisher but can't seem to find it on XP. Any answers please be gentle not great on computers Publisher doesn't come with the OS if that's what you mean by XP. If your new computer came with a Office 2003 you "may" have Publisher on the machine depending on which version of Office. "Rowan" <Rowan@discussions.microsoft.com> wrote in message news:7AD224EF-4D1C-491B-A563-150A475B52F0@microsoft.com... | Just got a fancy new computer and am used to using Microsoft 2000. Ofte...

how do I get rid of the "AVG certification" attachment ?
this attachment is on every email I send out It says something a virus not being found. How do I get rid of it? Thanks Consult the AVG application's documentation.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "golfer" <golfer@discussions.microsoft.com> wrote in message = news:B2C544F1-8162-4F44-A7A5-73FE51429781@microsoft.com... &g...

getting max value
select TrainDetails.ch_TrainDetailsID_pk, TrainDetails.ch_TrainDetailsID_fk, TrainDetails.ch_CurrentYrNo, TrainDetails.ch_CurrentWkNo, TrainDetails.ch_PlanYr, TrainDetails.ch_WON_WkNo, TrainDetails.ch_PPSwrksiteTSR_ref, TrainDetails.ch_NROL_PTO_ref, TrainDetails.ch_VehicleType, TrainDetails.ch_TrainID, TrainDetails.ch_RunDate from traindetails GROUP BY TrainDetails.ch_TrainDetailsID_pk having ch_currentWkNo > max(ch_currentWkNo) - 3; Hi all I'm trying to output data out by comparing the current week no (which is an integer type not a date type) with the maximum week n...

Icons changed in Favorites since update
Just updated windows latest update - and now the icons in my favorites have changed to a "square with a star" - at least 80% are like this - what happened. Thanks for anyhelp ...

how to update calendar in publisher 2007
Why doesn't calendar wizard update the dates when you change the month? It only changes the month, so I end up starting from scratch. What am I missing? sjn The calendar wizard works okay, it is there on the right when you select calendars. You may have to scroll down a little. It says "Set Calendar Dates" The Design Gallery calendars wizards are missing, this is a bug I hope will be fixed in the service pack. If you are asking about custom dates and appointments, they will have to be typed manually on each month, or copy and pasted. -- Mary Sauer MSFT MVP http://office...

DPM 2007 : Failed to update end-user recovery permissions
I have DPM 2007 installed on Windows 2008 SP2 backing up a file server also running Win2k8 SP2 (able to backup other systems without issue) Everytime it runs a sync job it produces a warning which is instantly resolved saying that ================================================ Status: Active Computer: BE-FS-01.ad.domain Description: Failed to update end-user recovery permissions. The operation failed because of a protection agent failure. For more information, open DPM Administrator Console and review the alert details in the Monitoring task area. ======================...

What happened to the column number count in the status bar?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I am new to using Office with a mac, and I have a lame question. I am using Word 2008. There used to be options as to what counts and tools you could put at the bottom of the window. Such as, word count, page number, column number etc. I don't see any options to customize the status bar. Am I missing something? It used to be as easy as right clicking (w/PC) and selecting which tool you wanted. How do I do this with my Mac??? Any help is appreciated. -B Yeah, evidently the guy/gal in charge of those features was out...

Using the Where clause in an update query
Code below was entered into the "Update To" field while writing a query in design view (Wizard)...As a result the "...operand without and operator..." dialogue box shows up. The intent is to update a number identification field to: "061-" & forms!main!line &"-"& equipmentlistings!numberset where forms!main!equipment = equipmentlistings!equipmenttype &"-"& forms!main!method Equipmenttype and Numberset are from the same table, equipmentlisting, all stored as text. Equipmenttype is chosen from a form. I&#...

NumberFormat not getting changing onFly changing the regional sett
Hi Excel experts , Sorry for posting a new question over here. I am having a hard time with one of a Excel related issue. Requirement: 1) User pulls some data ( includes numeric,Date fields ) to excel when he/she is with German OS settings. Date show in following format: dd.mm.yyyy ( eg: 23.09.2010) 2) Now on the fly he/she changes the OS regional settings to En-US 3) Here our code again reformats those columns with current regional settings and polish the data according to its regional settings. 4) In our case all numeric values are getiing changed correctly to the ...

Error code when you use WU/MU to install updates: 0x737D
Error code when you use Windows Update or Microsoft Update to install updates: "0x737D" (30 Dec-09) <QP> SYMPTOMS When you install updates by using Windows Update or Microsoft Update, you receive the following error code: 0x737D Additionally, you may receive the following error message: SQL Server Setup cannot upgrade the specified instance because the previous upgrade did not complete. Note: Microsoft SQL Server 2005 may be installed on your computer when you install applications such as *Windows Live Photo Gallery* and *Windows Live Mail*. [Doh! S...

Importing numbers from 200 worksheets into 1
I wish to import numeric data from numerous worksheets, say upto 200, into an overall summary page. For example, I have 200 projects, thus each worksheet is named Prj1, Prj2, etc. In each of these I enter the numbers of hours that the workforce book to these projects. The list of workforce members can be up to 100 different designations, and different projects necessarily do not use the same combination of the workforce. For example: Column C Column E Prj1 row 17 lead electrical engineer 200 Row 23 Senior mechanical engineer 450 Row 51 Junior piping engineer 700 Pr...

How Can A User Get NDR from PostMaster With Our Setup?
Exc 2003 as smtp only - we are using a spam filtering company and have firewall set to only accept emails from them - they are our mx. This company also filters viruses, attachments, etc. A user is getting an NDR that appears to be from Postmaster@ourdomain. It's being returned because of an attachment violation (it's a PIF file). The email address is not her normal one, but one of a group (jobp). This email address is being spoofed but how can she get emails from our Postmaster? I don't understand that. Her system has not been breached, she has AV on her desktop (it's ...

Windows Mail has changed and I don't like it, how can I get it bac
My windows page has changed. I used to be able to double click on the sender and get the messaget displayed in the entire window. What happens is I have to scroll down on the sidebar to see the entire message. I want my mail to look as it originally did. Double click on the mail item in the list to open it in its own separate window. Were you talking about viewing in the "preview pane" when you mentioned using the scroll bar to view the entire message? You can disable that at View - Layout - Reading / Preview Pane - uncheck the "show" option. "...