SQL If no value for current date then select next closest date

[athyeh]

Hi Folks,

Was wondering if somebody could help polish this dummy query, such that if there are no records with a datestamp = user-selection, the query returns a record with the next closest date

SELECT DateStamp
FROM 
TableA  
WHERE 
DATESTAMP = Userselected

[June7]

Next closest to the user input?

Maybe:

SELECT Max(DateStamp) AS DS FROM TableA WHERE DateStamp<=Userselected;

[athyeh]

Thank you for your help, the code below returns all records, less than the user selected datestamp (Not the max).

Tried writing the select statement Max(Datestamp) [not Max(datestamp, datestamp)] but was getting a 'Select field not valid error' for all select fields after Datestamp

SELECT DISTINCT  
 MAX( Datestamp, Datestamp ) AS Datestamp,FROM 
 TableA
 RIGHT OUTER JOIN TableB
 ON 
  PID = PID
  
WHERE 
 DATE( Datestamp, CYYMMDD ) <= &Datestamp
UNION SELECT DISTINCT  
 MAX( Datestamp, Datestamp ) AS Datestamp,
FROM 
 TableA
 RIGHT OUTER JOIN TableB
 ON 
  PID = PID
  
WHERE 
 DATE( Datestamp, CYYMMDD ) <= &Datestamp

[pbaldy]

If you want closest, regardless of whether it's more or less, I do this type of thing (note this is SQL Server syntax, slightly different than Access):

strSql = "SELECT TOP 1 * FROM CabManager.dbo.Meter_Transactions M " _
       & "WHERE M.VehicleID = " & Me.cboVehicle _
       & " ORDER BY ABS(DATEDIFF(minute, M.Transaction_Timestamp, '" & Me.DateTimeField & "'))"

The keys being the TOP predicate and ordering by the absolute difference in time.

[June7]

I do not understand

Max(Datestamp, Datestamp)

nor

Date(Datestamp, CYYMMDD)

What you show in post 3 is far different from the request in post 1.

Does Paul's suggestion help?