I'm importing data from an Access DB file to Excel using VBA and I'm struggling with a WHERE clause on one of my queries. This is not so much a technical difficulty. It is more like a strategy difficulty.
Right now, after establish the connection,
Dim prv As String: prv = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=..." & _
"Jet OLEDB:Database Password=..."
Dim DBcon As New Connection
DBcon.Open prv
I use the next query:
Dim BDrcs As New Recordset
RC01.Open "SELECT * from Table1 WHERE Field1=Dateserial(2020,5,26)", DBcon
For the purposes this is intended, the code is working fine! However, my goal is to get Table1 values WHERE Field1 is the greater date before 2020/05/26 case there's not any entries with the date 2020/05/26.
Is it possible to do this on one single query?
Please don't bother to reply an alternative method (not that I don't want it, is just not necessary). I know I could always previously get all the data from the table (or, at least, all the data before 2020/05/26), check for the greater date and then get the data again. I'm simply trying to get the data with just one query, if it's possible at all...
If using Max doesn't work, you could try a sub query using TOP. Something like:
SELECT A.*
FROM Table1 AS A
WHERE A.Field1=
(SELECT DISTINCT TOP 1 B.Field1 FROM Table1 AS B
WHERE B.Field1<=DateSerial(2020,5,26)
ORDER BY B.Field1 DESC)
Regards,