Decimal Places In Calculated Field For Ms Access 2013

[deepakg27]

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

[June7]

Format property is just for display. The data is not changed. The field is calculating 0 to 4 decimal places. Try the Fixed setting.

For instance: 639.86 x 142.08 = 90911.3088

Fixed format will show 90911.31 but the full value of 90911.3088 will be used in calcs.

[deepakg27]

Hi June7,

Thanks for the insight. But I need to store the data for upto two decimal places only (Like Round function with ,2). How can i do it?

Thanks for your help. Much Appreciated.

Regards
Deepak Gupta

Format property is just for display. The data is not changed. The field is calculating 0 to 4 decimal places. Try the Fixed setting.

For instance: 639.86 x 142.08 = 90911.3088

Fixed format will show 90911.31 but the full value of 90911.3088 will be used in calcs.

[June7]

Then you can't use a calculated type field in table. Saving calculated data is usually unnecessary and/or ill-advised.

Use code (VBA or macro) to save the calculated value. The calculation is simple. The real trick is figuring out what event to put the code into.

Or calculate rounded value when it is needed.

[ssanfu]

The top two lines of EVERY module should be

Option Compare Database
Option Explicit

"Date" and "Type" are reserved words in Access and shouldn't be used as object names. I renamed them.

Having "ID" as the PK field name of every table is a poor naming convention. I renamed them (and the FK fields)

See the relationship image......



You have the "tblTaxRate" table PK field as an Autonumber, but the 3 related field(s) in "tblSalesDetail" are Text Type. Cannot set RI.


You have Calculated fields and LookUp fields in your tables. I NEVER use Calculated fields and LookUp FIELDs.
See:
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[deepakg27]

Respected Ssanfu,

Thanks for your guidance. I will try me level best to adhere to the rules of data normalization. Thanks for guiding me in the right direction. And special thanks to make my data look so beautiful and professional.

Sir, in your data design could you please tell me what does RI stands for. Should I shift the 3 wrong field types to Number data type?

Secondly, if we don't use lookup fields, how to show the name of the customer and information related to him in the invoice, while entering data.

Thanks and Regards
Deepak Gupta

The top two lines of EVERY module should be

Option Compare Database
Option Explicit

"Date" and "Type" are reserved words in Access and shouldn't be used as object names. I renamed them.

Having "ID" as the PK field name of every table is a poor naming convention. I renamed them (and the FK fields)

See the relationship image......



You have the "tblTaxRate" table PK field as an Autonumber, but the 3 related field(s) in "tblSalesDetail" are Text Type. Cannot set RI.


You have Calculated fields and LookUp fields in your tables. I NEVER use Calculated fields and LookUp FIELDs.
See:
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[ssanfu]

Sir, in your data design could you please tell me what does RI stands for.

RI stands for Referential integrity. See https://www.webopedia.com/TERM/R/ref...integrity.html and/or https://en.wikipedia.org/wiki/Referential_integrity
If you do not have RI set, you just have a line between two fields.
With RI set, you will see 1 at one end of the linking line (relationship) and the infinity symbol for the many side of the relationship. (1 to many)

Should I shift the 3 wrong field types to Number data type?

If you want to set RI, then yes.

Secondly, if we don't use lookup fields, how to show the name of the customer and information related to him in the invoice, while entering data.

I would use a main form/sub form arraignment; combo boxes would be used to select the data. In the main form (based on tblSales), select the customer/buyer. In the sub form (based on tblSalesDetails), select the invoice items from the combo boxes.

[deepakg27]

Thanks Ssanfu.

I would try and adhere to your suggestions. Will try to avoid lookup fields in future. I use lookup fields, as I work directly with tables. I will try and shift my working to forms.

Thanks for the guidance. Looking for more in future.

Regards
Deepak Gupta

RI stands for Referential integrity. See https://www.webopedia.com/TERM/R/ref...integrity.html and/or https://en.wikipedia.org/wiki/Referential_integrity
If you do not have RI set, you just have a line between two fields.
With RI set, you will see 1 at one end of the linking line (relationship) and the infinity symbol for the many side of the relationship. (1 to many)



If you want to set RI, then yes.



I would use a main form/sub form arraignment; combo boxes would be used to select the data. In the main form (based on tblSales), select the customer/buyer. In the sub form (based on tblSalesDetails), select the invoice items from the combo boxes.