Double quotes within a variable string

[Duck]

I am trying to define an variable to hold an SQL query and am having
all sorts of trouble when I open a recordset with the variable.

The original query is:

SELECT tblTimes.ID, tblTimes.UserName, tblTimes.TimeIn,
tblTimes.TimeOutLunch, tblTimes.TimeInLunch, tblTimes.TimeOut,
tblTimes.MiscOut, tblTimes.MiscIn
FROM tblTimes
WHERE (((tblTimes.TimeIn) Like CDate(Format(Date(),"mm/dd/yyyy")) &
"*"));

My vaiable definition looks like this:

strSql = "SELECT tblTimes.ID, tblTimes.UserName, tblTimes.TimeIn,"
strSql = strSql & " tblTimes.TimeOutLunch, tblTimes.TimeInLunch,"
strSql = strSql & " tblTimes.TimeOut, tblTimes.MiscOut,
tblTimes.MiscIn"
strSql = strSql & " FROM tblTimes"
strSql = strSql & " WHERE (((tblTimes.TimeIn)"
strSql = strSql & " Like CDate(Format(Date(),"mm/dd/yyyy")) &
"*"))"

rs.open strSql, cnn, adOpenDynamic, adLockOptimistic

I know the problem centers around the double quotes in the last line,
and I have tried all sort of variations to try to resolve the problem
to no avail...when I remove the WHERE clause the query works fine, but
with the clause I get an EOF or BOF error message, which I pretty sure
indicates that there are no records being returned for the recordset.
Can anyobdy help me PLEASE!!

 

[Douglas J. Steele]

Check what Allen Browne has at http://www.allenbrowne.com/casu-17.html

However, you cannot use Like with the Date datatype: it only works with Text
fields.

 

[Sylvain Lafontaine]

When you have a problem like this, you should display the result of the
strSQL string in the debug (Immediate) window or in a message box in order
to see is there anything wrong with it. In your case, try something like:

strSql = "SELECT tblTimes.ID, tblTimes.UserName, tblTimes.TimeIn,"
strSql = strSql & " tblTimes.TimeOutLunch, tblTimes.TimeInLunch,"
strSql = strSql & " tblTimes.TimeOut, tblTimes.MiscOut, tblTimes.MiscIn"
strSql = strSql & " FROM tblTimes"
strSql = strSql & " WHERE (((tblTimes.TimeIn)"
strSql = strSql & " Like CDate(Format(Date(),""mm/dd/yyyy"")) & ""*""))"

You could also replace the usage of doubling any embedded double quote by
using either a unique single quote or using the Chr(34) or Chr(39) syntaxe:

strSql = strSql & " Like CDate(Format(Date(), 'mm/dd/yyyy')) & '*'))"

strSql = strSql & " Like CDate(Format(Date(), " & Chr(34) & "mm/dd/yyyy"
& Chr(34) & ")) & '*'))"

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)