If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
percentage calculation...trying again
Can anyone help please? Report Gross Grand Total and Sales tax Total are off
by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#2
|
|||
|
|||
percentage calculation...trying again
This is not an uncommon problem. What causes this is that each line item
calculation does it's own rounding. But, when you take the total number and perform the calculation, the rounding can easily cause it to be off by a penny or two. The usual technique to cure this is to sum the columns involved rather than redo the calculation. -- Dave Hargis, Microsoft Access MVP "JMS" wrote: Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#3
|
|||
|
|||
percentage calculation...trying again
Currency is four decimal, so multiplication of two currency numbers will
require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#4
|
|||
|
|||
percentage calculation...trying again
I guess I don't understand because I thought I was doing that already in my
report footer. Please explain....thanks for the reply. "Klatuu" wrote in message ... This is not an uncommon problem. What causes this is that each line item calculation does it's own rounding. But, when you take the total number and perform the calculation, the rounding can easily cause it to be off by a penny or two. The usual technique to cure this is to sum the columns involved rather than redo the calculation. -- Dave Hargis, Microsoft Access MVP "JMS" wrote: Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#5
|
|||
|
|||
percentage calculation...trying again
Hi Michel
I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#6
|
|||
|
|||
percentage calculation...trying again
Was missing a ). Should have been:
SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(sa lesTaxeRate)*100)) / 100 Now, instead of: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 I would try: Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*100))/100+ Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*CDec([SalesTaxRate])*100))/100+ First(Int(0.5+[FreightCharge]*100))/100 Note that I don't know why you use FIRST, for the last part, I would have expect MAX, or MIN, ... if not SUM. Vanderghast, Access MVP "JMS" wrote in message ... Hi Michel I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#7
|
|||
|
|||
percentage calculation...trying again
Still have that same error on the right parenthesis ([Quantity])
On the "First" part, I was taking advice from John Vinson because the freightcharge was multiplied by the number of items in the order. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Was missing a ). Should have been: SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(sa lesTaxeRate)*100)) / 100 Now, instead of: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 I would try: Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*100))/100+ Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*CDec([SalesTaxRate])*100))/100+ First(Int(0.5+[FreightCharge]*100))/100 Note that I don't know why you use FIRST, for the last part, I would have expect MAX, or MIN, ... if not SUM. Vanderghast, Access MVP "JMS" wrote in message ... Hi Michel I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#8
|
|||
|
|||
percentage calculation...trying again
Is
SUM(CDec(quantity)) alone, produces the same error? It may be either that quantity is null, either you have a problem of VBA references (or something else I don't see at the moment). If quantity is null (there is nothing in it), try: SUM(CDec(Nz(quantity, 0) ) ) If you have a problem of VBA reference, open the VBE window (generally, Alt-F11, or Ctrl_G will do), then, from the menu: Tools | References... you get a list of references. Among those that are checked, see if one of them start its description with the word MISSING. If so, uncheck it. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Still have that same error on the right parenthesis ([Quantity]) On the "First" part, I was taking advice from John Vinson because the freightcharge was multiplied by the number of items in the order. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Was missing a ). Should have been: SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(sa lesTaxeRate)*100)) / 100 Now, instead of: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 I would try: Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*100))/100+ Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*CDec([SalesTaxRate])*100))/100+ First(Int(0.5+[FreightCharge]*100))/100 Note that I don't know why you use FIRST, for the last part, I would have expect MAX, or MIN, ... if not SUM. Vanderghast, Access MVP "JMS" wrote in message ... Hi Michel I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#9
|
|||
|
|||
percentage calculation...trying again
Michel:
Sorry to be a pain but I have tried SUM(CDec(quantity)) and SUM(CDec(Nz(quantity, 0) ) ) with the same error message. Originally my order details table had the default value for quantity set to blank but I tried it with 0 and 1 as well. I also checked the VBA References as you instructed and I didn't see anything with the word "MISSING" in the description. I wanted to thank you again for helping...I really appreciate it. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Is SUM(CDec(quantity)) alone, produces the same error? It may be either that quantity is null, either you have a problem of VBA references (or something else I don't see at the moment). If quantity is null (there is nothing in it), try: SUM(CDec(Nz(quantity, 0) ) ) If you have a problem of VBA reference, open the VBE window (generally, Alt-F11, or Ctrl_G will do), then, from the menu: Tools | References... you get a list of references. Among those that are checked, see if one of them start its description with the word MISSING. If so, uncheck it. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Still have that same error on the right parenthesis ([Quantity]) On the "First" part, I was taking advice from John Vinson because the freightcharge was multiplied by the number of items in the order. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Was missing a ). Should have been: SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(sa lesTaxeRate)*100)) / 100 Now, instead of: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 I would try: Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*100))/100+ Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*CDec([SalesTaxRate])*100))/100+ First(Int(0.5+[FreightCharge]*100))/100 Note that I don't know why you use FIRST, for the last part, I would have expect MAX, or MIN, ... if not SUM. Vanderghast, Access MVP "JMS" wrote in message ... Hi Michel I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
#10
|
|||
|
|||
percentage calculation...trying again
So, to summarize:
SELECT SUM(CDec(Nz(quantity,0))) FROM Orders GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate produces an error, when you switch your query from SQL view to data view? If you, can you send me a (reduced) version of your database. Be sure to remove any confidential data from it :-). You can send it to VanderghastArrobasMsnDotCom Vanderghast, Access MVP "JMS" wrote in message ... Michel: Sorry to be a pain but I have tried SUM(CDec(quantity)) and SUM(CDec(Nz(quantity, 0) ) ) with the same error message. Originally my order details table had the default value for quantity set to blank but I tried it with 0 and 1 as well. I also checked the VBA References as you instructed and I didn't see anything with the word "MISSING" in the description. I wanted to thank you again for helping...I really appreciate it. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Is SUM(CDec(quantity)) alone, produces the same error? It may be either that quantity is null, either you have a problem of VBA references (or something else I don't see at the moment). If quantity is null (there is nothing in it), try: SUM(CDec(Nz(quantity, 0) ) ) If you have a problem of VBA reference, open the VBE window (generally, Alt-F11, or Ctrl_G will do), then, from the menu: Tools | References... you get a list of references. Among those that are checked, see if one of them start its description with the word MISSING. If so, uncheck it. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Still have that same error on the right parenthesis ([Quantity]) On the "First" part, I was taking advice from John Vinson because the freightcharge was multiplied by the number of items in the order. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Was missing a ). Should have been: SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(sa lesTaxeRate)*100)) / 100 Now, instead of: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 I would try: Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*100))/100+ Sum(Int(0.5+CDec([Quantity])*CDec([UnitPrice])*CDec([SalesTaxRate])*100))/100+ First(Int(0.5+[FreightCharge]*100))/100 Note that I don't know why you use FIRST, for the last part, I would have expect MAX, or MIN, ... if not SUM. Vanderghast, Access MVP "JMS" wrote in message ... Hi Michel I have been using the following in my query: Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 to get my gross order total which includes the salestax and freight. Are you saying format it like this and how would I include the FreightCharge? I get an error when I put this in my query "The expression you entered has a function containing the wrong number of arguments." and then the cursor goes to the right parenthesis on quantity. SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 thanks for taking the time help...sorry, not real good with access "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Currency is four decimal, so multiplication of two currency numbers will require 8 decimals to be exact. Furthermore, your quantity field is a double-float, and the whole double multiplication in Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 is converted to a double-float representation, thanks to quantity not being a currency neither an integer, BEFORE being converted back to currency. Instead, try: SUM( INT( 0.5+ CDec(quantity) * CDec(unitPrice) * CDec(salesTaxeRate) * 100 ) / 100 since a DECimal number allows more room for decimal digits, that Currency, and the double multiplication working only with CDec, results in a CDec result. The INT( x *100) /100 truncates (does not round, just truncate) the result to 2 decimals. I used INT(0.5 +x*100)/100 to round, rather than to truncate. Hoping it may help, Vanderghast, Access MVP "JMS" wrote in message ... Can anyone help please? Report Gross Grand Total and Sales tax Total are off by a penny or 2 The amounts are being generated by the sum expression below that calculate unitprice and salestax so users only need to put the unit price of the item in the Order Details Table. The line totals in the report seem to calculate and display properly but the grand totals are off by a few pennies. The data type for unit price and sales tax rate is set to currency and 2 decimal places and will not allow input mask changes. The Main Report Query gross Order Total shows a number like 667.3768 Thanks ----Order Details Table---- OrderDetailID - Primary Key - AutoNumber - Long Integer OrderID - Text ProductID - Text Quantity - Number - Double - Decimal 0 UnitPrice - Currency - Format Currency - Decimal 2 CCSPrice - Currency - Format Currency - Decimal 2 Discount - Currency - Format Currency - Decimal 2 ----Order Table---- OrderID - Text CustomerID - Number - Long Integer - Decimal Auto EmployeeID - Long Integer - Decimal Auto OrderDate - Date/Time - Short Date PurchaseOrderNumber - Text ShipName - Text ShipAddress - Text ShipCity - Text ShipStateOfProvince - Text ShipPostalCode - Text ShipCountry - Text ShipPhoneNumber - Text ShipDate - Date/Time - Short Date ShippingmethodID - Number - Long Integer 0 Deciaml Auto FreightCharge - Currency - Decimal 2 CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0 ARDetailID - AutoNumber - Long Integer ----Main Report Query---- SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100 AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS [Sales Tax Total], Orders.FreightCharge, Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total], Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing, Orders.OrderDate, Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100 AS Profit FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge, Orders.OrderDate HAVING (((Orders.OrderDate)=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)=[forms]![Report Date Range]![Ending Order Date])); In the Main Report Query, each record for Gross Order Total is showing 4 numbers to the right of the decimal. ----Main Report---- Record Source - Main Report Query Filter on - No Order By On - No Sorting and Grouping - OrderDate - Ascending Group Header - No Group Footer - No Group on Each Value Group Interval - 1 Keep Together No In Report Detail I have Gross Order Total and Sales Tax Total which seem to calculate fine...I think. The calculations are incorrect when I Sum those 2 in the Report footer. This is where I am seeing it off by 1 or 2 pennies. =Sum([Gross Order Total]) - Running Sum - No =Sum([Sales Tax Total]) - Running Sum - No |
Thread Tools | |
Display Modes | |
|
|