Passing a date variable to a SQL backend?

[DataEmbryo]

Hello,

I have a MS Access Form that has a date text box which is used in a number of queries for a report. The criteria used in the queries was similar to this:

Select [Date]
From tblblah
where [Date] Between [Forms]![Process]![txtDate] And [Forms]![Process]![txtDate]-13

The txtDate variable is a short date format in the form and the date field was also in short date format.

This worked well when the table (tblblah) was in the Access Database, but I recently moved all the data to a SQL Server and then ODBC linked to the SQL tables from my Access db. The Date field is formatted in SQL Server as a smalldatetime.

When I try to run the series of queries similar to the example above, they return no results. If I manually enter a date instead of the criteria to use txtDate, results are returned.

Can you help me understand why this is not working?


Thank you for your time,
Mark

 

[Trevil]

Did you really use "-13" or is that a typo? I have linked tables to SQLSVR, used your format of SQL but with +13 and it works just fine. Change to -13 and zero records.

35+ years of 'progress' -- can't we all just go wire boards again?