Subtotals

[Lori LeRoy]

I have a sales order report that I want to display the units ordered subtotal
if the ship quantity is zero and/or the line item is open; and the units
shipped if the quantity shipped is greater than zero and/or the line item is
closed. (if any quantity is shipped that line item is closed). currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the line
item is open or quanities have been shipped, creating a larger total for the
entire report. I'm sure this is a simple if statement, but I'm at a loss.
Thanks.

 

[Duane Hookom]

Can you provide some field names, data types, sample records, and desired
display in the report?

 

[Lori LeRoy]

I just found this posting - I apologize for the late response.

The sorting & grouping in the report is as follows:
SalesOrderID
Openlineitem
ItemNo
ProductID

The detail in the report is:
itemorder
SOTransactionID
Shipdate
UnitsOrdered
UnitsShipped
OpenLineItem
price
SubtotalSO

and the footer is
=Sum[(SubtotalSO)]

Thank you,
Lori

Can you provide some field names, data types, sample records, and desired
display in the report?


--
Duane Hookom
MS Access MVP

I have a sales order report that I want to display the units ordered
subtotal
if the ship quantity is zero and/or the line item is open; and the units
shipped if the quantity shipped is greater than zero and/or the line item
is
closed. (if any quantity is shipped that line item is closed). currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the line
item is open or quanities have been shipped, creating a larger total for
the
entire report. I'm sure this is a simple if statement, but I'm at a loss.
Thanks.

 

[Duane Hookom]

We don't know what defines a line item as "open". I guess that "ship
quantity" is "UnitsShipped"? There are no fields named UnitsOrderedSO and
UnitPriceSO.

You need to talk using field names or at least define the fields. Also,
field types might be important or possible values.

--
Duane Hookom
MS Access MVP

I just found this posting - I apologize for the late response.

The sorting & grouping in the report is as follows:
SalesOrderID
Openlineitem
ItemNo
ProductID

The detail in the report is:
itemorder
SOTransactionID
Shipdate
UnitsOrdered
UnitsShipped
OpenLineItem
price
SubtotalSO

and the footer is
=Sum[(SubtotalSO)]

Thank you,
Lori

Can you provide some field names, data types, sample records, and desired
display in the report?


--
Duane Hookom
MS Access MVP

I have a sales order report that I want to display the units ordered
subtotal
if the ship quantity is zero and/or the line item is open; and the
units
shipped if the quantity shipped is greater than zero and/or the line
item
is
closed. (if any quantity is shipped that line item is closed).
currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the
line
item is open or quanities have been shipped, creating a larger total
for
the
entire report. I'm sure this is a simple if statement, but I'm at a
loss.
Thanks.