Report Calculation

[Guest]

I have an existing report that spits out a master carton label. On this
label is the QTY of items inside the carton (Case Pack is a field in one of
my tables). The current report spits out the exact number of labels I need
to match my carton count. For example: If a customer orders 24 units and my
case pack is 12 units. My report will give me 2 labels indicating 12 units
on each. Again, the 12 units is not a calculated field but pulling from a
field in my table.

My problem comes when a customer orders 30 units and my case pack is 12
units. I want the report to give me three labels total: (2) at 12 units and
(1) at 6 units. Right now my report will give me 3 labels at 12 units each.

Any suggestions??

Thanks,
Jack

 

[Ken Snell \(MVP\)]

What mathematical expression are you using to calculate the number of
labels? Tell us what you're using right now, and then we probably can help
identify a way to do what you seek.

 

[Guest]

I have the following code in the module of my report. "CalcQTY" is the
number of labels for each item the report prints. CalcQTY = Item PO QTY /
Master Carton QTY.

For example a customer orders 1200 of an item that has a master carton qty
of 12. The report will spit out 100 labels that show a master carton qty of
12. I want the report to calculate the "Case QTY" based on the PO QTY and
Master QTY. That is, if a customer order 1206 of the same item above. I
need the report to generate 100 labels at "12" Case QTY and the 1 label at
"6" Case Qty.

-------
Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < CalcQTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub

I hope this isn't too confusing. I really do appreaciate any help you can
provide.

Thanks again,
Jack

 

[Ken Snell \(MVP\)]

I am not fully understanding how this code creates the result that you
describe, as I don't know what the query's data records are that the report
is using as its RecordSource. But I would suggest that you approach the
problem a bit differently -- instead of trying to have the report
"calculate" the contents for the labels, it would be better if you used a
query that produces records with the right "quantities" in the data records,
and then let the report just print those records without having to try to
calculate "partial" quantities in the report.

Without knowing how the report gets "started", I can only suggest an
approach. Assuming that you have a form and you click a button on the form
to print the report, let the programming in that button's click event
generate a query or table with the correct quantity values for each label to
be printed (the programming can easily calculate a "partial" value), and
then base the report on that query or table.