Query to select a closest date

[Babycat]

Hi Everyone.

How to build a query that select ProductID with the closest date to a dynamic entered date (from a textbox) as picture below

Please help me

Thank you.

 

[plog]

This will need a subquery to find the specific CreatedDate that meets the criteria:

SELECT MIN(CreatedDate) AS ClosestDate FROM Q_NXT_StartP WHERE CreatedDate<=[Forms]![YourFormNameHere]![DateInputNameHere]

Rename that 'sub1', then use it to build another query to obtain the data you want:

SELECT ProductID FROM Q_NXT_StartP INNER JOIN sub1 ON Q_NXT_StartP.Created_Date=sub1.ClosestDate

In case of a tie--as in ProductID=38, 40, 43, &45--all records will be returned. To return only 1 result you will need to provide a logic way to differentiate the one you want returned.

 

[MajP]

Not sure if you want a query or function. Either way it would be using some logic where you selecting the first record that is less than the selected date.

Here is a rough function

Public Function GetClosest(dtmDate as date) a Integer
  dim rs as dao.recordset
  dim strSql as string
 'Sort largest first 
 strSql = "Select Top 1 ProductID from SomeTable where CreatedDate <= #" & format(dtmDate,"mm/dd/yyyy") & "# ORDER BY CreatedDate DESC"
  set rs = currentDB.openrecordset(strSql)
  if not RS.EOF then GetClosest = rs!ProductID
end function

 

[MajP]

@plog. I think you would want the max.

 

[plog]

I am even wronger than that. Thinking twice about it, he said "closest" not the "immediate prior" record. Therefore he should use the absolute value of DateDiff and another subquery:

So, throw away both my and MajP's prior advice and use this:

sub1:

SELECT ProductID, CreatedDate, Abs(DateDiff("d", [Forms]![YourFormNameHere]![YourInputNameHere], CreatedDate)) As DifferenceFromTarget
FROM Q_NXT_StartP

sub2:

SELECT MIN(DifferenceFromTargetDate) AS ClosestDate FROM sub1

Main:

SELECT ProductID FROM sub1 INNER JOIN sub2 ON sub1.DifferenceFromTarget=ClosestDate

 

[MajP]

The label does say closest but smaller.

 

[Babycat]

Sorry to make you confused, I should correct it to "closest and smaller". The "CreatedDate" field is actually the Order creadted-date of products.
Each product would have many orderIDs therefore many "CreatedDate".
I want query's result with productIDs and orderdate which made before textbox-date.
For example:
Textbox-date is: 27/09/2021
Result will be 2 records: (43 ; 25/09/2020) and (45 ; 26/09/2020), because these ProductIDs have order before 27/9
Textbox-date is: 29/09/2021
Result will be 4 records: (38; 28/09/2020; ( 40; 28/09/2020), (43; 28/09/2020) and (45; 28/09/2020) these ProductIDs have order before 29/9 and these order created-date are closest to 29/9

Sorry my bad english, hope you it understandable

 

[Babycat]

SELECT ProductID, CreatedDate, Abs(DateDiff("d", [Forms]![YourFormNameHere]![YourInputNameHere], CreatedDate)) As DifferenceFromTarget
FROM Q_NXT_StartP

is that OK with function abs here? It might return the record with greater than "textbox" input date...

 

[plog]

The correct queries would be this:

sub1:

SELECT MAX(CreatedDate) AS ClosestDate FROM Q_NXT_StartP WHERE CreatedDate<=[Forms]![YourFormNameHere]![DateInputNameHere]

Main:

SELECT ProductID FROM Q_NXT_StartP INNER JOIN sub1 ON Q_NXT_StartP.Created_Date=sub1.ClosestDate