VB-Access-SQL Date format problem help please

[sdh]

I have a database (Access) which has a table in it created with the following sql statement in vb code

sqltext = "CREATE TABLE Reports (ID COUNTER CONSTRAINT Recid PRIMARY KEY, RepDate datetime,report_name text,"
sqltext = sqltext & " Userid text, Country text,Unit text,Yr text(2),Wk text(2),Reptype text,Repdesc text,Path text,access text)"


when i run the query from vb using the statement below I return nothing even though there are reports in there that match the date can anyone tell me why?

sqltext = "Select * from reports where Repdate=#" & Date & "#"

please can someone help this is driving me up the wall.
sdh :

 

[Diogo]

Try this!

sqltext = "Select * from reports where Repdate=#" & CStr(Format(Date, "yyyy-mm-dd&quot) & "#"

DR

 

[sdh]

Thanks Diogo works perfectly.
However I would like to understand why.

what do I need to create a string in that format?

sdh:

 

[Diogo]

In your sqltext you have a string field and a date field.
Whith the command cstr i'm converting a date field to a string.

DR