percentage calculation...trying again

J

JMS

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
 
G

Guest

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 said:
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
Click to expand...
 
M

Michel Walsh

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 said:
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
Click to expand...
 
J

JMS

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

JMS

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Michel Walsh

Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

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 said:
Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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 said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

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 said:
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 said:
Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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 said:
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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

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 said:
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 said:
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 said:
Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

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 said:
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 said:
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 said:
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.


Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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
:)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

yeah the error is there. I will send a copy of the database to you minus my
multimillion dollar contract info :) Can't say thank you enough for your
help. I will send it your way here in just a bit.


Michel Walsh said:
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 said:
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 said:
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

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.


Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had it as
VanderghastArrobasMsnDotCom and thought the "s" should have been a "t". Let
me know when you get it on your end....thanks



Michel Walsh said:
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 said:
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 said:
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

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.


Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


JMS said:
Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had it
as VanderghastArrobasMsnDotCom and thought the "s" should have been a "t".
Let me know when you get it on your end....thanks



Michel Walsh said:
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 said:
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.





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

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.


Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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
:)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

Yeah got that part but I didn't see the "at" replacement in the email you
gave. (VanderghastArrobasMsnDotCom) I sent it to the following:
VanderghastArrobatMsnDotCom
VanderghastArrobasatMsnDotCom
If you don't get it please send an email to jscavarda at hotmail dot com and
I will reply. Sorry for the confusion.



Michel Walsh said:
You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


JMS said:
Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had it
as VanderghastArrobasMsnDotCom and thought the "s" should have been a
"t". Let me know when you get it on your end....thanks



Michel Walsh said:
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


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.





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

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.


Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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
:)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

Both failed. Please send an email to jscavarda at hotmail dot com and I will
reply....Thanks


JMS said:
Yeah got that part but I didn't see the "at" replacement in the email you
gave. (VanderghastArrobasMsnDotCom) I sent it to the following:
VanderghastArrobatMsnDotCom
VanderghastArrobasatMsnDotCom
If you don't get it please send an email to jscavarda at hotmail dot com
and I will reply. Sorry for the confusion.



Michel Walsh said:
You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


JMS said:
Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had
it as VanderghastArrobasMsnDotCom and thought the "s" should have been a
"t". Let me know when you get it on your end....thanks



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


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.





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

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.


message Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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
:)


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



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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

@ == arrobas ! Well, wikipedia call it arroba...
(http://en.wikipedia.org/wiki/Arroba)

I mailed to you, anyhow, that should solve that problem :)

Vanderghast, Access MVP


JMS said:
Both failed. Please send an email to jscavarda at hotmail dot com and I
will reply....Thanks


JMS said:
Yeah got that part but I didn't see the "at" replacement in the email you
gave. (VanderghastArrobasMsnDotCom) I sent it to the following:
VanderghastArrobatMsnDotCom
VanderghastArrobasatMsnDotCom
If you don't get it please send an email to jscavarda at hotmail dot com
and I will reply. Sorry for the confusion.



Michel Walsh said:
You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had
it as VanderghastArrobasMsnDotCom and thought the "s" should have been
a "t". Let me know when you get it on your end....thanks



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


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.





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

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.


message Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Seems there is indeed a problem with calling CDec, in that case. A
workaround is to define a cover function in a VBA standard module. I am
sending you the db with the modifications.


Vanderghast, Access MVP


Michel Walsh said:
@ == arrobas ! Well, wikipedia call it arroba...
(http://en.wikipedia.org/wiki/Arroba)

I mailed to you, anyhow, that should solve that problem :)

Vanderghast, Access MVP


JMS said:
Both failed. Please send an email to jscavarda at hotmail dot com and I
will reply....Thanks


JMS said:
Yeah got that part but I didn't see the "at" replacement in the email
you gave. (VanderghastArrobasMsnDotCom) I sent it to the following:
VanderghastArrobatMsnDotCom
VanderghastArrobasatMsnDotCom
If you don't get it please send an email to jscavarda at hotmail dot com
and I will reply. Sorry for the confusion.



You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


Email sent to VanderghastArrobatMsnDotCom. hope that is right. You had
it as VanderghastArrobasMsnDotCom and thought the "s" should have been
a "t". Let me know when you get it on your end....thanks



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


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.





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

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.


message Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

Zip and re-send when you get a chance...hotmail blocked...thanks.


Michel Walsh said:
Seems there is indeed a problem with calling CDec, in that case. A
workaround is to define a cover function in a VBA standard module. I am
sending you the db with the modifications.


Vanderghast, Access MVP


Michel Walsh said:
@ == arrobas ! Well, wikipedia call it arroba...
(http://en.wikipedia.org/wiki/Arroba)

I mailed to you, anyhow, that should solve that problem :)

Vanderghast, Access MVP


JMS said:
Both failed. Please send an email to jscavarda at hotmail dot com and I
will reply....Thanks



Yeah got that part but I didn't see the "at" replacement in the email
you gave. (VanderghastArrobasMsnDotCom) I sent it to the following:
VanderghastArrobatMsnDotCom
VanderghastArrobasatMsnDotCom
If you don't get it please send an email to jscavarda at hotmail dot
com and I will reply. Sorry for the confusion.



You use @, not the word itself, same for dot, . :)

I haven't get anything at the time of this post.

Vanderghast, Access MVP


Email sent to VanderghastArrobatMsnDotCom. hope that is right. You
had it as VanderghastArrobasMsnDotCom and thought the "s" should have
been a "t". Let me know when you get it on your end....thanks



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


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.





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

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.


message Was missing a ). Should have been:

SUM(INT(0.5+CDec(quantity)*CDec(unitPrice)*CDec(salesTaxeRate)*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


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 :)


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



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report totals off by a couple cents. 4
percentage calculation 2
Total Query 2
adding freight amount to total 14
...Ambiguous Field Reference..... 2
Make table query 1
Trying to summarise orders and payments 1
Invalid Use of Null 1

Top