Query returns results in QBE, but not via VBA code

Question

Working in Access 2010 against an Access DB, I created a query in the QBE. As part of an overly complex quarterly reporting process, I now need to be able to execute that query with different parameters via VBA code. The SQL for the query is now embedded in my VBA module, and is modified on the fly, then executed. When run in the QBE, this particular instance of the query returns 400+ rows of data, but none are returned when executed in VBA via this code:

Dim Data As New ADODB.Recordset
Dim SQLString As String
SQLString = "SELECT PatientStatSchedDataDump.PtCompID, AppType.ProviderW, " & _
                   "PatientStatSchedDataDump.Date, PatientStatSchedDataDump.Status " & _
              "FROM (AppType INNER JOIN PatientStatSchedDataDump ON AppType.AType = " & _
                   "PatientStatSchedDataDump.Type) LEFT JOIN GroupType ON AppType.Group = " & _
                   "GroupType.Group " & _
             "WHERE (((PatientStatSchedDataDump.PtCompID) Like 'ClientName*' ) " & _
               "AND ((PatientStatSchedDataDump.Date) BETWEEN #1/1/2014# AND #3/31/2014#) " & _
               "AND ((GroupType.[Sort Order]) IN ('A', 'B', 'C', 'D', 'E')))"
Data.Open Source:=SQLString, ActiveConnection:=CurrentProject.Connection
If Not Data.EOF And Not Data.BOF Then
'the IF is never true - EOF & BOF are always True
  NewSheet.Cells(InstCountRow + InstCount + 2, 1).CopyFromRecordset Data
End If
Data.Close
Set Data = Nothing

Again, if I create a new query in Access, paste the SQL code into the SQL window and run it, I get 400+ rows of results with this exact query

HansUp HansUp · Accepted Answer · 2015-03-06T20:56:10

A query run from ADO requires ANSI wild cards: % instead of *; and _ instead of ?.

So change this ...

"WHERE (((PatientStatSchedDataDump.PtCompID) Like 'ClientName*' ) "

to this ...

"WHERE (((PatientStatSchedDataDump.PtCompID) Like 'ClientName%' ) "

If you want one query which works the same when run from ADO as it does when run in the QBE, you can use ALike instead of Like. With ALike, the db engine always expects ANSI wildcards.

"WHERE (((PatientStatSchedDataDump.PtCompID) ALike 'ClientName%' ) "

Query returns results in QBE, but not via VBA code

1 Answers