Getting correct date from query results

[riggsdp]

I need to figure out an event's starting date based on the last time the item was delivered.



For example, if an item is due monthly, there may be 40 previous deliveries, but I only want to find the last delivery date so I can add a month to that date for the current delivery.

My very first delivery has its due date calculated by getting the start date of the delivery or task order using DLookup, then calculating the due date through a SetDueDate function.

startDate = DLookup("Start_Date", "tblDeliveryTaskOrders", "Task_ID = " & Forms!frmDeliveriesReceived!frmDeliveriesReceived_Subform.Form!Delivery_Task_Order)
SetDueDate = FindDueDate(eventDue, startDate)

After it is delivered, the next delivery needs to get the previous delivery date from the delivery table with the same Relate_ID:

I have the following query (qryGetLastDelivered) to get the last date for each set of items:

SELECT tblDeliveriesReceived.Relate_ID, Max(tblDeliveriesReceived.Date_Due) AS [Total Of Date_Due]FROM tblDeliveriesReceived
GROUP BY tblDeliveriesReceived.Relate_ID;

So, how do I get the start date I need from the query results where

"Relate_ID = " & Forms!frmDeliveriesReceived!frmDeliveriesReceived_Subform.Form!Relate_ID)

Thanks in advance.

[riggsdp]

Modified my query to this and it gets the date I need, now just need to pass that date to my code:

SELECT tblDeliveriesReceived.Relate_ID, Max(tblDeliveriesReceived.Date_Due) AS [Total Of Date_Due]FROM tblDeliveriesReceived
GROUP BY tblDeliveriesReceived.Relate_ID
HAVING (((tblDeliveriesReceived.Relate_ID)=[Forms]![frmDeliveriesReceived]![frmDeliveriesReceived_Subform].[Form]![Relate_ID]));

[rpeare]

if you are saying you want to look up the 'start date' from two different tables under different conditions.
1. An 'order' has been placed (something exists in tbldeliverytaskorders) but no deliveries have ocurred (nothing in table tblDeliveriesReceived)
2. An 'order' has been placed and deliveries exist

you could do something in your function like

if dcount("*", "tbldelivereisreceived", "[Relate_ID] = " & forms!frmdeliveriewsreceived!frmdeliveriesrecieved_subform.form!relate_ID) = 0 then
    'do what you're going to do if nothing exists in the tbldeliveriesreceived table
else
    'do what you're going to do if something exists in the tbldeliveriesreceived table
endif

I don't know how well this would work if you're using a continuous bound subform (I don't use bound forms). in the first portion of the IF statement you could have your existing code startdate = blah/setduedate (eventdue, startdate), in the second portion you could just lookup the maximum date with dmax("date_due", "tbldeliveriesreceived", "[Relate_ID] = " & <form qualifier> )

[riggsdp]

Thanks, I already have that part taken care of, but what I need is how to pass the date result from the query back to my code - which is what would be done if a delivery already exists.

For example: startDate = result from qryGetLastDelivered

[rpeare]

I think I addressed that

startdate = dmax("[due_date]", "tblDeliveriesReceived", "[Relate_ID] = " & [Forms]![frmDeliveriesReceived]![frmDeliveriesReceived_Subform].[Form]![Relate_ID])

you don't need the query qrygetlastdelivered at all if you use the dmax function

[riggsdp]

Okay, now I understand! Thanks and will try it out.