Solved Calculate Box X of Y with quantaties (1 Viewer)

[Poco_90]

Good afternoon. I am hoping some one can help, as I am at a complete loss. I have tried searching for this, but its not the easiest term to look up as it pulls results about counting combo boxes etc. I have being trying queries and count loops but with no success.

I am trying to create labels which show box x of y on the label plus the individual box quantity. I have the information below, which should generate 5 records.

BoxQty	TotalQty
20​	85​

From the data above(from a form) I was looking to return the data below, which I will link to my label templates.

Qty	X of	of Y
20​	1​	5​
20​	2​	5​
20​	3​	5​
20​	4​	5​
5​	5​	5​

Any help would be greatly appreciated.
Regards
Poco

 

[jdraw]

Suggest a function to get total boxes required.

Public Function Ceiling(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to get Ceiling
    ' is the multiple to which you want to round
   
     Ceiling = (Int(x / Factor) - (x / Factor - Int(x / Factor) > 0)) * Factor
End Function

Usage:
TotalBoxes = Ceiling(TotalQty,BoxQty)

Sample:

' ----------------------------------------------------------------
' Procedure Name: CeilTest
' Purpose: test routine to show labels for boxes
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 03-Sep-21
' ----------------------------------------------------------------
Sub CeilTest()
    Dim i As Integer: i = 1
    Dim boxQty As Integer: boxQty = 20
    Dim totalqty As Integer: totalqty = 85
    Dim boxes As Integer
    boxes = Ceiling(totalqty / boxQty)
    Do While i <= boxes
        Debug.Print "Box " & i & " of " & boxes
        i = i + 1
    Loop
End Sub

Result:

Box 1 of 5
Box 2 of 5
Box 3 of 5
Box 4 of 5
Box 5 of 5

 

[arnelgp]

see this demo.

 

[Poco_90]

Jdraw and Arnelgp, Thank you for your quick responces. I am nearly afraid to say how long I spent at this. I am spoilt, code and a working database. Thanks again.

 

[Poco_90]

Back again,
Just wondering arnelgp is it possible to modify your loop code to insert fixed data from the form I am running this off? I want to add 5 or 6 fields to the table, barcode, customer name etc etc.

I tried adding it into your code but keep getting syntax errors. I also tried a 2nd insert after i = i +1, this kind of worked, it added the data I requird to the table but on alternative rows to the rows with content and label calculations.
Thanks in advance,
Poco

i = 1
    
    Do Until lngTotal < 1
        lngContent = min(lngTotal, CLng(Me.txtContent))
       CurrentDb.Execute "insert into zzTable (content, label) " & _
            "select " & lngContent & ",'Box " & i & " of " & Me.txtBoxes & "'"
        lngTotal = lngTotal - lngContent
        i = i + 1
    Loop

End goal, would be like this

ID	Barcode	Content	Label
213​	12345	200​	Box 1 of 3
214​	12345	200​	Box 2 of 3
215​	12345	100​	Box 3 of 3

 

[Poco_90]

Not the prettiest of code, but got this to work

i = 1
    
    
    Do Until lngTotal < 1
        lngContent = min(lngTotal, CLng(Me.txtBoxQty))
       CurrentDb.Execute "insert into zzTable (content, label) " & _
            "select " & lngContent & ",'Box " & i & " of " & Me.txtBoxes & "'"
        lngTotal = lngTotal - lngContent
        i = i + 1
    Loop
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE zzTable SET [CustCode]= '" & Me.txtCustCode & "',[CustName]= '" & Me.txtCustName & "';"
    DoCmd.SetWarnings True

 

[arnelgp]

you can combine it inside the Loop:

i = 1
    
    
    Do Until lngTotal < 1
        lngContent = min(lngTotal, CLng(Me.txtBoxQty))
       CurrentDb.Execute "insert into zzTable (custCode, CustName, content, label) " & _
            "select '" & Me!txtCustCode & "','" & _
            Me!txtCustName & "'," & lngContent & ",'Box " & i & " of " & Me.txtBoxes & "'"
        lngTotal = lngTotal - lngContent
        i = i + 1
    Loop

 

[Poco_90]

Ahh arnelgp, just when I had my ugly code written!

I prefer your way much cleaner, I just couldn't get it to work when I tried, more than likely where I was putting my operators. Thanks for this, its a great help.

 

[Poco_90]

Hi arnelgp
I found an issue with the loop, that I hope you can help me with.

If the total order quantity equals the box quantity, the total number of boxes should be 1 but the loop is adding +1 to the total number of boxes. Code is perfect if the qty in the last box is an odd number.

Hope below explains better. Thanks in advance
Poco

Total Qty = 10 and Box Qty = 10

ID	Content	Label	CustCode	CustName	OurPartNo	Description	CustomerPartNo	LBLDate	Week
346	10	Box 1 of 2	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721

Total Qty = 30 and Box Qty = 10

ID	Content	Label	CustCode	CustName	OurPartNo	Description	CustomerPartNo	LBLDate	Week
347	10	Box 1 of 4	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721
348	10	Box 2 of 4	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721
349	10	Box 3 of 4	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721

Total Qty = 25 and Box Qty = 10

ID	Content	Label	CustCode	CustName	OurPartNo	Description	CustomerPartNo	LBLDate	Week
350	10	Box 1 of 3	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721
351	10	Box 2 of 3	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721
352	5	Box 3 of 3	99999​	Raleigh	BK-876	Bike	ABC12345	09/09/2021	3721

 

[arnelgp]

change the code:

    CurrentDb.Execute "delete * from zzTable;"
    
    Me.txtBoxes = (Me.txtTotal \ Me.txtContent) + (((Me.txtTotal Mod Me.txtContent) > 0) * -1)
    lngTotal = Me.txtTotal

    Do Until lngTotal < 1
        i = i + 1
        lngContent = min(lngTotal, CLng(Me.txtBoxQty))
       CurrentDb.Execute "insert into zzTable (custCode, CustName, content, label) " & _
            "select '" & Me!txtCustCode & "','" & _
            Me!txtCustName & "'," & lngContent & ",'Box " & i & " of " & Me.txtBoxes & "'"
        lngTotal = lngTotal - lngContent
    Loop

 

[June7]

Try:

Me.txtBoxes = Me.txtTotal \ Me.txtContent + IIf(Me.txtTotal Mod Me.txtContent = 0, 0, 1)

Now I see arnel got back on this.

 

[Poco_90]

Thank you, works perfectly now.

 

[Poco_90]

Thanks June7, Only spotted your repliey after I replied to arnel

 

[June7]

I expect arnel's version is more efficient because it is not calling another function - IIf(). Very nice.

 

[arnelgp]

we can remove the * -1:

Me.txtBoxes = (Me.txtTotal \ Me.txtContent) - ((Me.txtTotal Mod Me.txtContent) > 0)

 

[Poco_90]

Thanks again for all your help with this.