Date criteria in ADO code causing problems

[P_Henry]

Hi There,

I have been struggling to make this bit of code work...is there something wrong in it?:

rst.Open ("Select * from tblusers where (tblusers.fromdate <= #" & Date & "#) AND (tblusers.toddate >= #" & Date & "#)")

I want to be able to select records which have from date < current date and to date greater than current date.

The table is located on SQL server and linked to Access front end and the From date and To date fields are datetime fields.

Is there anything wrong with this code?
The whole thing works fine on a query but it is the code that is causing a problem.
Could you please throw some light?
Thanks,
Priya

 

[pbaldy]

Looks okay. What error do you get? Is there really an extra "d" in the to date?

 

[P_Henry]

Hi,
The records are not picked up as expected...the < and > signs do not seem to be doing their tasks.

Thanks,
P

 

[pbaldy]

You mentioned linked; are you using linked tables? If you're connecting directly, SQL Server would want single quotes around the dates rather than #.

 

[P_Henry]

I am using linked tables to Access front end . That's why I am using the # character.

 

[pbaldy]

You don't have a field in your table named "Date" do you? Can you post a sample db?

 

[ChrisO]

>>rst.Open ("Select * from tblusers where (tblusers.fromdate <= #" & Date & "#) AND (tblusers.toddate >= #" & Date & "#)")<<
>>I want to be able to select records which have from date < current date and to date greater than current date.<<

If you want less than don’t use less than or equal, if you want greater than don’t use greater than or equal.

Tomorrow (the 13th in Australia) it might work okay, but in the meantime try: -

"Select * From tblusers Where (tblusers.fromdate < " & CDbl(Date) & ") And (tblusers.toddate > " & CDbl(Date) & ")"