Variable (date) to Recordset SQL (Where) clause to retrieve data

[Chris Hallett]

Hello
My problem lies with accessing data by using the Where clause on a SQL statement when opening a Recordset.

I can pass the date restriction fine when I enter the date directly I.E.

adoPrimaryRS.Open
"select Date,Time,Traff,Outgoing,Incoming
from InOut
where Date = ' 99-09-09 '", db, adOpenStatic, adLockOptimistic

However I want the user to select a date, which is passed to a variable (this passes OK to a Label for example). I am receiving an empty recordset with the following;

adoPrimaryRS.Open "select Date,Time,Traff,Outgoing,Incoming
from InOut where Date = ' " & strTest & " '", db, adOpenStatic, adLockOptimistic

Where am I going wrong??
Any help Greatly appreciated
Chris

[velkave]

I haven't tried ti with ado, but have done with RDO. Sometimes enclosing the where condition alone in paranthesis helps. Also, I think you may have to leave a space in between the last two quotes like

adoPrimaryRS.Open "select Date,Time,Traff,Outgoing,Incoming
from InOut
where (Date = ' " & strTest & " ')", db, adOpenStatic, adLockOptimistic
If you're doing this from outside the USA then maybe you have to send the variable value as ' " & datevalue(strTest) & " '.