Orders to Be Automatically Grouped Based on Order Date

[hellocng]

Hi, this is my first time on this forum and am unsure where to post this question to.



I have a form where the user enters the order information. If the orders are submitted within 24-hours from the first order, they are shipped together.

I would like the user to enter the orders, and based on the time of the order, determine which orders are shipped together.

For example, order A was at 10/1/17 12:00 PM; order B was at 10/1/17 4:00 PM, order C was at 10/2/17 2:00 AM; order D was at 10/2/17 2:00PM.

Order A, B, C would be shipped together; let's have a field called shipmentID, and it is shipment A. Order D will be filled in the next shipment, shipment B.

The shipmentID can be in any form - autonumber, etc , as long as I can tell that they are together. How would I do this?

[ranman256]

put this code into a module, or button OnClick event:

the code below uses a query qsItems2Ship of all available orders where [ShipBlock] = null
'then cycles thru the query, getting one 24 time block at a time, filling in the ShipBlock

Public Sub MakeShipBlocks()
Dim vStartDate, vEndDate
Dim lBlock As Long, lCnt As Long
Dim sSql As String
Const kQRY = "qsItems2Ship"    'this query gets all orders to ship in date order, with null SHIPBLOCK
   
docmd.Setwarnings = false
lBlock = 1
bMore = True
vStartDate = DMin("[OrderDate]", kQRY)                       'get the 1st Timestamp of the orders
vEndDate = DateAdd("s", -1, DateAdd("h", 24, vStartDate))    'get the end time of the 1st block
lCnt = DCount("*", kQRY)


While lCnt > 0
    sSql = "UPDATE tOrders SET tOrders.ShipBlock = " & lBlock & " WHERE (((tOrders.OrderDate) Between #" & vStartDate & "# And #" & vEndDate & "#) AND ((tOrders.ShipBlock) Is Null));"
    DoCmd.RunSQL sSql
    
    lBlock = lBlock + 1
    vStartDate = DateAdd("s", 1, vEndDate)  'get next 24hr time block
    vEndDate = DateAdd("s", -1, DateAdd("h", 24, vStartDate))  
    lCnt = DCount("*", kQRY)
Wend

MsgBox "Done"
End Sub

[hellocng]

Thank you for the help!

I forgot to mention in the original question, the orders are shipped together as long as the customerID is the same. So if orders A, B, C, and D are not for same customer, there needs to be separate shipblock. I can't figure out how to use a query to separate the customers. Also, once the shipblocks are set for all orders, can I use this code with a button to enter new orders and auto-assign the shipblock?