UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Query Drops Cents, Any Versions    
 
   
bakersburg9
post Oct 30 2018, 01:29 PM
Post#1



Posts: 5,258
Joined: 2-November 04
From: Downey, CA


I ran an append query that updates a linked table in SharePoint - the data source is a table where the data type for the Invoice Amount field is Double / 2 Decimel places - this is only the 2nd time I've done this, with no success - 160 dollars even shows up as "160," not "160.00"

How can I remedy this ?

Edit: Sorry for the "weak" quality of the screen shot, but you get the idea . . .
This post has been edited by bakersburg9: Oct 30 2018, 01:30 PM
Attached File(s)
Attached File  SharepointDropsZeros.png ( 30.74K )Number of downloads: 6
 
Go to the top of the page
 
Doug Steele
post Oct 30 2018, 02:29 PM
Post#2


UtterAccess VIP
Posts: 21,987
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Unless I'm missing something, your complaint seems to be about formatting, not value, because 160 and 160.00 are both the same value.

If this impacts your ability to display the values on a form, set the Format property of the text box.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
bakersburg9
post Oct 30 2018, 02:43 PM
Post#3



Posts: 5,258
Joined: 2-November 04
From: Downey, CA


QUOTE
set the Format property of the text box.
there's no text box - it is about formatting - I have a table with records in Access - I created an Append query to append to sharepoint via a table link - the data shows as 160.00 in the table - the datatype is "Double" set to 2 decimel places when I run the query, it updates sharepoint fine, but the amounts show as 160, not 160.00 - don't know what it would do if there were some actual cents...
Go to the top of the page
 
GroverParkGeorge
post Oct 30 2018, 04:05 PM
Post#4


UA Admin
Posts: 33,958
Joined: 20-June 02
From: Newcastle, WA


As Doug said, I'm pretty sure it's a FORMAT FOR DISPLAY issue. If you have values that include pennies, they should show up. Is that NOT happening now with your test data?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
Doug Steele
post Oct 30 2018, 06:25 PM
Post#5


UtterAccess VIP
Posts: 21,987
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Have you checked whether it captures cents correctly? I'm betting you find it does...

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
BruceM
post Oct 31 2018, 09:13 AM
Post#6


UtterAccess VIP
Posts: 7,724
Joined: 24-May 10
From: Downeast Maine


QUOTE
there's no text box

Are you saying you aren't seeing the decimal portion when viewing the table directly? If so, you can format the table field, but the place to apply the format is wherever you display the data, which shouldn't be the table directly. As has been suggested/asked, what happens when you import a value that includes decimal values?
QUOTE
the datatype is "Double" set to 2 decimal places

Currency would be a better choice, most likely. You can get unexpected results because of rounding errors when working with Double (or Single) values. In my experience these are most likely to cause problems with comparisons. A string of mathematical operations may show the result 5.00, but is really 5.00000001. This matters if you are using <= 5 as a criteria, for instance. It looks like the value is <= 5, but behind the scenes Access will interpret it as > 5. Currency data type avoids this potential problem, which can be quite vexing.
Go to the top of the page
 
bakersburg9
post Oct 31 2018, 09:23 AM
Post#7



Posts: 5,258
Joined: 2-November 04
From: Downey, CA


Thanks! I'm going to try Currency next time - we'll see what happens
Go to the top of the page
 
BruceM
post Oct 31 2018, 09:46 AM
Post#8


UtterAccess VIP
Posts: 7,724
Joined: 24-May 10
From: Downeast Maine


OK, good luck. Just keep in mind that how data are stored and how they are displayed are different things.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2018 - 04:14 PM