Query works in SQL fails in VB code !?!

[Dorian]

This has had me stuck for 2 days! I have a query that I build/run in VB and
it returns no rows (gets EOF). I copy the SQL to the query designer and it
returns 13 rows. Anyone got any idea why before I go crazy?

SQL:
SELECT P.ID
FROM ((tblPackages AS P
INNER JOIN tblPkgTypes AS T ON T.ID = P.TypeID)
INNER JOIN tblPkgDates AS D ON D.PkgID = P.ID)
INNER JOIN tblPkgDateTypes AS DT ON DT.ID = D.DateTypeID
WHERE T.PkgType LIKE '*5-Year*'
AND (D.ActualDate >= #1/1/2008# AND D.ActualDate <= #12/31/2008#
OR (D.TargetDate >= #1/1/2008# AND D.TargetDate <= #12/31/2008#
AND D.ActualDate IS NULL))
AND DT.DateType = 'Research begin'

VB:
strSQL = "SELECT P.ID" & _
" FROM ((tblPackages AS P" & _
" INNER JOIN tblPkgTypes AS T ON T.ID = P.TypeID)" & _
" INNER JOIN tblPkgDates AS D ON D.PkgID = P.ID)" & _
" INNER JOIN tblPkgDateTypes AS DT ON DT.ID = D.DateTypeID" & _
" WHERE T.PkgType LIKE '*5-Year*'" & _
" AND (D.ActualDate >= #1/1/2008# AND D.ActualDate <=
#12/31/2008# OR (D.TargetDate >= #1/1/2008# AND D.TargetDate <= #12/31/2008#"
& _
" AND D.ActualDate IS NULL))" & _
" AND DT.DateType = 'Research begin'"
rs2.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Not rs2.EOF Then
MsgBox rs2.RecordCount, , "# Packages opened for 5-Year Review"
Else
MsgBox "EOF", , "EOF"
End If
rs2.Close

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Danny Lesandrini]

It's easy to add a debug statement after the SQL assignment and then grab
the SQL text from the debug window.

1) After you set the value for strSQL, add this line ...
Debug.Print strSQL
2) Run your code

3) Press Ctl+G to show the Immediate window and get the string output

4) Paste THAT sql into a query and see where it balks.

 

[Dorian]

No need to do that, I know what the result will be.
It seems you have to use LIKE '*string*' in query designer but LIKE
'%string%' in VB execute.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".