Using Date conditions in Access SQL

[ramanan1986]

Hi,

I have a combo box (cmbDate) in my form which fetches date values from the Access tables, and the code goes like this:

cmbDate.RowSource = "Select Distinct Report_Date " & _
"From Table1 " & _
"Where Report_Date between #f_dt# and #t_dt# " & _
"Order by Report_Date Desc"

f_dt and t_dt are in this date format = 2/28/2013

And this works perfectly fine.

But for some reason, we had to move the tables in the Access to SQL Server 2008 R2. So, we set up a connection between the Server and Access and have linked the tables. Now, the form is supposed to fetch the data from the tables linked to this SQL server. Everything else works perfectly fine. My only problem is fetching the dates in cmbDate. The above syntax (with # on both sides of the date field) does not work. I replaced them with single quotes (') as shown below:

cmbDate.RowSource = "Select Distinct Report_Date " & _
"From Table1 " & _
"Where Report_Date between 'f_dt' and 't_dt' " & _
"Order by Report_Date Desc"

f_dt and t_dt are in this date format = 28-02-2013

But this does not fetch any dates and the cmbDate remains empty. I have tried playing around with the format of the dates, but hasn't succeeded.

Additional information, if it helps:
When I look at the tables in the SQL server. It is in the Date-Time format (i.e. the Report_Date column has values 2013-02-28 00:00:00.000 (YYYY-MM-DD HH:MM:SS). When this table is linked to Access, in Access it is displayed the Date format (i.e. the Report_Date column has values like 28-02-2013 (DD-MM-YYYY)


Any help on what should the format be like??

Regards

 

[DavidAtWork]

try this:
cmbDate.RowSource = "Select Distinct Report_Date " & _
"From Table1 " & _
"Where Report_Date between '" + Format(f_dt, "YYYY-MM-DD hh:mm:ss") + "' and '" + Format(t_dt, "YYYY-MM-DD hh:mm:ss") + "' " & _
"Order by Report_Date Desc"

David