Find the Nearest Date from another table

[wizzz_wizzz]

Hi All,

I have a table tblSPM in which the field [Supplier_Delivery_Date] is updated frequently. On another table - tblShipmentDates,
I have a field [Shipment_Dates], which is a list of schedule to make shipment to customers.

Basically, I need a query to find the next nearest date (from Shipment_Dates], depending on [Supplier_Delivery_Date].

Can someone point me to the right direction how can I go about doing it?




Appreciate any advise. Thank you!

[ranman256]

in a query, join the 2 tables on the CustID field.
bring down dates from both tables, and create a field" 'difference' to calc the #days between them.

difference:=DateDiff("d",tSPM.Supplier_Delivery_Da te, tblShipmentDates.Shipment_Dates)

sort the results ASC. These are the dates closest. (smallest difference)

[Micron]

If that doesn't work for you because of table design or whatever, it should be possible by creating a query with a subquery. Basically, the sub query gets the nearest date (less or greater than depending on the expression used) as long as there's at least one common field between the two tables. If the topic of sub queries is new to you, check this out
http://allenbrowne.com/subquery-01.html