Using IsVisible property (1 Viewer)

Darrell

Registered User.
Local time
Today, 11:01
Joined
Feb 1, 2001
Messages
299
Going down a death spiral on this one and have finally given up...

I have a report based on a query of orders vs deliveries. For each item in the order there may be several deliveries as they can be spread over a time period.

So in the report, it shows duplicate lines for the order item (Inv Code) and order qty and of course then gives the wrong totals for order qty.

The order I am looking at has only one Inv Code and an Order Qty of say 500.
The delivery is split over 3 dates. Qtys are 400, 50 and 50.
On the report it is showing 3 rows and I have set the property of the Inv Code to HideDuplicates.

I have added the following
Code:
Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)

If Me!InventoryCode.IsVisible Then
    Me![Qty Ordered].Visible = True
Else
    Me![Qty Ordered].Visible = False
End If

End Sub

And this works fine.

However, the sub total for the Qty Ordered (in a Customer order footer) is showing 1,500 so I would like to use the same property to make it only show the actual value but have hit a brick wall with how to go about it.

All help greatly appreciated. Thanks
 

JHB

Have been here a while
Local time
Today, 12:01
Joined
Jun 17, 2012
Messages
7,732
Which amount contains [Qty Ordered], 500?
If yes you need to sum the control/field which contain the 400, 50 and 50.
 

Darrell

Registered User.
Local time
Today, 11:01
Joined
Feb 1, 2001
Messages
299
Hi there.

Qty Ordered is 500
Qty Delivered is split in three deliveris - 400, 50, 50

So the sum of Qty Delivered is correct - 500
But the sum of Qty Ordered is 1,500

I know that this is from the query ouput where it has duplicated the Inv Code and Qty 3 times due to the 3 deliveries. I just cant work out how to make the report ignore the duplicates.
 

JHB

Have been here a while
Local time
Today, 12:01
Joined
Jun 17, 2012
Messages
7,732
Then set the control source to [Qty Ordered] instead of Sum([Qty Ordered]).
 

Darrell

Registered User.
Local time
Today, 11:01
Joined
Feb 1, 2001
Messages
299
Thanks but this wont do what is required. The example is just for one Inventory Code but the report can show several and therefore needs to total [Qty Ordered].
Hence the reason I wanted to use a sum of the visible records.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2013
Messages
16,553
try using avg for quantity ordered rather than sum
 

Darrell

Registered User.
Local time
Today, 11:01
Joined
Feb 1, 2001
Messages
299
I don't see how that would work.
If I have two other Inventory Code records with say 100, and 200, respectivly, then If I summed the box it would be 1,800, but if I average it it will be 360, whereas it needs to be 800.

What I have done is as follows and it appears to work.
Firstly, made the textboxes for the Footer and Report totals unbound.
Secondly, changed the code
Code:
Public QtyTotal, RptQtyTotal As Long

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
If Me!InventoryCode.IsVisible Then
    Me![Qty Ordered].Visible = True
    QtyTotal = QtyTotal + [Qty Ordered].Value
    RptQtyTotal = RptQtyTotal + [Qty Ordered].Value
Else
    Me![Qty Ordered].Visible = False
End If
End Sub

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
[Cust_Qty_Ordered_Sum] = QtyTotal
QtyTotal = 0
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
[Rpt_Qty_Ordered_Sum] = RptQtyTotal
End Sub

So thanks anyway, but this has solved my problem
 

Users who are viewing this thread

Top Bottom