Using transact SQL in Access project

[vbkoder]

Can anyone tell me if I can use transact SQL statments like CAST, CONVERT in
my code module in the Access project. I have a selection form where you enter
a start and end date that will open a report. The problem is that the SQL
database has the date fields as varchar's at the moment. This is a temporary
report that my client wants until the database is completed. When trying to
filter the report, it does not work, I guess cause you can't filter a text
field. I wanted to use a CAST over to a datetime, but Access complains. Any
ideas of solutions or that it cannot be done would be appreciated.

Thanks

 

[Joe Fallon]

It depends on where the statement is executed (and how it gets there.)
In other words, if Jet is invovled you need to use Jet syntax.

But if you use SPT (SQL Pass Through) queries then Jet is not involved nad
the SQL is sent directly to the server. So you *have* to use Server syntax.
For SQL Server, that means you can use T-SQL.