Query Works but when in VBA, it does not?

[DThreadgill]

In Access 2003, I have a query set up like this:

SELECT [Comments],
       [Comment ID] AS [Unique ID],
       [Submitted Date]
 
FROM   tblMain
 
WHERE  ((([Submitted Date]) Between DateSerial(Year(Date()),Month(Date())-2,1)
                                And DateSerial(Year(Date()),Month(Date()),0))
  AND   ([Subject1]<>"Online")
  AND   ([Flags] Like "*Merger*"))
   OR  (([Submitted Date] Between DateSerial(Year(Date()),Month(Date())-2,1)
                              And DateSerial(Year(Date()),Month(Date()),0))
  AND   ([Subject1]<>"Online")
  AND   ([Flags] Like "*Merger*")
  AND   ([Type]<>"Online"));

This query runs just fine and brings back 121 records.

However, when I put it in VBA like this, it brings back 0 records:

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
strqry = "WHERE (((tblMain.[Submitted Date]) Between DateSerial(Year(Date()),Month(Date())-2,1) And
DateSerial(Year(Date()),Month(Date()),0)) AND ((tblMain.Subject1)<>'Online') AND ((tblMain.Flags) Like '*Merger*'))  " & _ "OR (((tblMain.[Submitted Date]) Between DateSerial(Year(Date()),Month(Date())-2,1) And DateSerial(Year(Date()),Month(Date()),0)) AND
((tblMain.Subject1)<>'Online') AND ((tblMain.Flags) Like '*Merger*') AND ((tblMain.Type)<>'Online'))" 
rst.Open _ Source:="SELECT tblMain.Comments, tblMain.[Comment ID] AS [Unique ID], tblMain.[Submitted Date] FROM tblMain " & strqry, _ CursorType:=adOpenKeyset, _ 
LockType:=adLockOptimistic, _ 
options:=adCmdText

If I remove both tblMain.Flags like '*Merger*' from the code, it works so I know it has something to do with that - just can't figure out what.

Any assistance appreciated!

[MikeTheBike]

Hi

As you are using an ADO recordset then change this
'*Merger*'
to this
'%Merger%'

and see if that fixes it [use * for DAO recordsets] !


MTB

[DThreadgill]

Perfect! Thank you Mike....this is the first I've used a wildcard in VBA coding so that was new to me!! Learn something new everyday!!!

[NeoPa]

Nice catch Mike :)

Alternatively of course, you could use DAO (instead of ADO). This is more compatible with Access's Jet SQL. ADO is more compatible with later standards though. See ANSI Standards in String Comparisons.