Do...Loop help!!

[Kingsteveiv]

Dear tek-tips friends.

I have a question that is a real stumper for me.

I have two fields in my database

1. Customer Qty - inputted data
2. Standard Pack Size - fixed data

When the customer qty is entered into the database it generates a duplicate report for each pack that needs to be shipped to the customer.

for example.

Customer Qty = 900 (units)
Standard Pack = 85 (units)

By dividing the Customer Quantity by the Standard Pack I get a value of 10.58, which means I will generate 11 reports. The question I have concerns the Standard Pack field that is generated on these reports. I want the first ten reports to display a standard pack of 85 units while I want the last report to display a standard pack of 50 units, since this will equal 900 total units exactly. If I just set that field on the report to equal the standard pack size of 85, then I will ship to many units (problem).

I believe the syntax would be in the form of a Do...Loop, but im not sure and I cant seem to get it to work. Is there any suggestions anyone has for me??

Please Help.

Thank you

 

[Mute101]

Not sure I fully understand this one.

What you could do (if you produce all the reports from one template) is;

keep a variable on the report which u populate with customer quantity. For each pass through your report you can remove the standard pack quantity form this total.

Therefore all you have to do is check the variable is greater than the standard pack size and if not display the quantity of your variable.

Dunno if that helps you.

Regards
Simon ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[matzos]

I think I have a decent grasp on this. Nothing like this EVER translates well.

Anyway, you could do a simple MOD calculation in the VBA to give you the remaining balance.

Create two variables, intX & intY for example, and use these calculations:

intX = 85 'YOUR STANDARD QTY
intY = 900 MOD 85 'YOUR REMAINING QTY

900 MOD 85 equals 50, which is what you need, I believe. Let me know how/if this works.

Good luck!