MySQL Query Not Working w/ VBA Recordset (1 Viewer)

[Kheribus]

Hello,

I have an app that is tallying transit information for dynamic invoice generation. I have a table of 'trips,' which is essentially an audit table, showing all values for each tripName key over time. The important fields are tripName and lastUpdate.

My program logic is like this:

Select a trip name (LS01).
On day i, I want to select each distinct individual trip in the table where tripName like 'LS01%' and the the top 1 date where date <= i.

So, table could be

Day i = 11/2/2017

LS01A, 11/1/2017
LS01A, 10/21/2017
LS01AP, 11/2/2017
LS01AP, 10/1/2017
LS01M1, 11/4/2017

returns a recordset with:

LS01A, 11/1/2017
LS01AP, 11/2/2017
... when day i = 11/4/2017, the table will include LS01M1, 11/4/2017 as well.

What i'm trying to do is accomplish this in one query without having to make an intermediary temptable. I have something like:

SELECT a.* FROM (SELECT * FROM tripslog where tripName like 'LS01%' and lastUpdate <= '2017-11-01') a 
LEFT OUTER JOIN (SELECT * FROM tripslog where tripName like 'LS01%' and lastUpdate <= '2017-11-01') b 
ON a.tripName = b.tripName AND a.lastUpdate < b.lastUpdate WHERE b.tripName IS NULL;

This works in mysql workbench, but it isn't working when I use that statement to create a recordset in VBA Access:

Set rsTemp = invoicing.OpenRecordset("SELECT a.* FROM " & _
                                    "(SELECT * FROM tripslog WHERE tripName like 'LS01%' and lastUpdate <= #" & invoiceDateArray(i) & "#) a " & _
                                    "LEFT OUTER JOIN (SELECT * FROM tripslog WHERE tripName like 'LS01%' and lastUpdate <= #" & invoiceDateArray(i) & "#) b " & _
                                    "ON a.tripName = b.tripName AND a.lastUpdate < b.lastUpdate WHERE b.tripName IS NULL;", dbOpenSnapshot)

When I run that recordset creation in VBA, it's giving me an object variable or with block variable not set error. All of my variables are correct. I'm sure that this isn't a data type kind of issue. There seems to just be some issue with the statement translating to VBA?

Any help would be greatly appreciated. Or, if there's a slicker way to do what i'm trying to do easily in MySQL, i would be greatly appreciative.

 

[Galaxiom]

The wildcard character for Access SQL is * rather than %.

 

[Cronk]

Also the date delimiter is # and the date should be formatted like #mm/dd/yyyy#

 

[JHB]

Have you declared and Set invoicing as a Database type and rsTemp as a Recordset type?
Else I'll suggest use the Debug.Print to determine how the SQL-string for the recordset looks like. Then you can put it into a new query then it is easier to find what goes wrong.

  Debug.Print "SELECT a.* FROM (SELECT * FROM tripslog WHERE tripName like 'LS01%' and lastUpdate <= #" & invoiceDateArray(i) & "#) a " & _
  "LEFT OUTER JOIN (SELECT * FROM tripslog WHERE tripName like 'LS01%' and lastUpdate <= #" & invoiceDateArray(i) & "#) b " & _
  "ON a.tripName = b.tripName AND a.lastUpdate < b.lastUpdate WHERE b.tripName IS NULL;"

 

[Kheribus]

Thanks all! It was the wild card character (should have been * not %). What a stupid mistake! Really appreciate the extra sets of eyes!