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
> Decimal Places In Calculated Field For Ms Access 2013, Access 2013    
 
   
deepakg27
post Nov 26 2017, 12:25 AM
Post#1



Posts: 54
Joined: 14-June 13



Hi Guys,

I am trying to make a simple invoicing database. I have created a calculated field Amount in tblSaleDetails where data type is set to double, Format to General Number & Decimal Places to 2. But still Access is calculating and saving upto four decimal places.

For your reference Data base is attached. Your help is much appreciated.

Thanks & Regards
Deepak Gupta
Attached File(s)
Attached File  data_Type.zip ( 181.91K )Number of downloads: 4
 

--------------------
Regards
Deepak Gupta
Go to the top of the page
 
projecttoday
post Nov 26 2017, 04:30 AM
Post#2


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


That's the way the double data type works. When you enter the price and quantity. which are also double, you are deliberately entering 2 decimal places. If you enter more decimals into price or quantity, you'll notice that it takes them. You need to switch to decimal or currency. You should not use double or single anyway due to a base 16 conversion which can occasionally produce a result which is off slightly.

Most Access developers don't use the Calculated data type either.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Nov 27 2017, 09:58 AM
Post#3


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Deepak,

The Double datatype is meant gigantic numbers or extremely tiny numbers, and is not really ideal for invoicing. Believe it or not, the Currency datatype is perfect for both your cost values as well as your quantities, as it is a fixed four-decimal place datatype.

As projecttoday already mentioned, generally you should not store calculated values in a database. Instead, you should calculate them on-the-fly in your queries, or on your forms and reports. For invoicing, you really only need the quantity (which can be fractional, which is why the 4-decimal-place Currency datatype is great), and the cost per item. In a printout of your invoice, you can include a calculated expression for the Extended Cost in your query (not your table) that multiplies the quantity times the cost to get the extended cost. Then, in either the report footer or a group footer you can use an expression that sums all of the Extended Cost values for the invoice's grand total.

If you need help setting up your tables and/or forms and reports, let us know. Invoicing is a very simple and common concept in Access databases.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
projecttoday
post Nov 27 2017, 10:31 AM
Post#4


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Okay, I hope you are all aware of the difference between calculated values and the calculated data type.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
projecttoday
post Nov 27 2017, 10:51 AM
Post#5


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I just want to make sure deepak understands you can have a calculated value with or without the calculated data type, which is something most Access developers never use, even the ones who store calculated values.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
deepakg27
post Nov 29 2017, 11:44 PM
Post#6



Posts: 54
Joined: 14-June 13



Thanks Project Today. I will try to adhere to your advice in future.

Could you please explain me how in a form I could store the value of the qty*rate in Amount field. I would prefer Saving calculate value in place of using calculated data type.

Thanks for your help.
Deepak Gupta

--------------------
Regards
Deepak Gupta
Go to the top of the page
 
deepakg27
post Nov 29 2017, 11:50 PM
Post#7



Posts: 54
Joined: 14-June 13



Thanks Dennis,

As suggested I have changed the data type to currency and decimal. Thanks for explaining me the difference in detail.

I am in process of making an input form for my tblSale & tblSaleDetails. Thanks for your help on setting up invoicing database. Incase any need arises i will come back to you for your help. Thanks. Meanwhile if you have any invoicing database which you could share, I would love to have a look at it.

Thanks and Regards
Deepak Gupta

--------------------
Regards
Deepak Gupta
Go to the top of the page
 
projecttoday
post Nov 29 2017, 11:57 PM
Post#8


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


With code. Maybe something like:

CODE
Me.txtAmount = 0
If Isnumeric(Me.txtQuantity) and Isnumeric(Me.txtRate) Then Me.txtAmount = Me.txtQuantity * Me.txtRate

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
John Vinson
post Nov 30 2017, 06:21 PM
Post#9


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


QUOTE
I would prefer Saving calculate value in place of using calculated data type.


Just be aware that this accomplishes three things, none good:

1. It wastes disk space.
2. It wastes computer time (almost any calculation is far faster than a disk fetch).
3. Most important, it risks having corrupt data in your table; if one of the underlying fields changes, your stored value is WRONG with no easy way to detect it.

I would not recommend the "calculated data type" in a Table, mainly because I would not recommend users looking at a table datasheet in any case; just do the calculation in a Query or in the control source of a Form or Report control.

The only time one should consider storing the result of a calculation is if that result is "tentative" - for example, a price which might be discounted or rounded up or down by the user, so that it CAN legitimately be different than the result of the calculation.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
projecttoday
post Nov 30 2017, 07:05 PM
Post#10


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I'll express an opposing view. My rule is that you don't store calculated values without reason as opposed to never storing calculated values. I believe that if you tell a customer "this is your amount" then you should consider recording that. Storing an amount on an invoice is not the same as storing every calculation made on every report as some novice programmers do. If you don't store it and you recalculate it later on there's a chance it could be calculated wrong. (Yes, I know it's difficult to mess up Quantity * Rate.) Also, if there is any performance gain by a calculation such as this, it is negligible. Anyway, that's how I feel. The choice is yours.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
John Vinson
post Dec 1 2017, 05:22 PM
Post#11


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


Very good point, Robert! A classic example is an "extended price" - cost plus sales tax, calculated like Round([Cost] * (1.0 + [TaxRate]), 2). Since the tax rate can change over time the value should be stored with the tax rate in effect at the time, rather than recalculated.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
deepakg27
post Dec 10 2017, 08:27 AM
Post#12



Posts: 54
Joined: 14-June 13



thanks for the insight.

Regards
Deepak Gupta

--------------------
Regards
Deepak Gupta
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 05:24 PM