How to query a MSSQL Server database using ODBC Access?

[Jacques Franken]

Hello everybody;

First of all, I'm a newby in generating SQL code. I'm trying to generate a query on a MSSQL database using Access 2007. I need to query a table on a DATE/TIME field. The problem is that the TIME part is not needed and I kinda would like to make a parameter-query where the users just need to enter the parameter a date (FORMAT dd/mm/YYYY). I'm living in Europe, Belgium to be more specific.

It should go something like this :
SELECT *
FROM dbo.tbl.InArch
WHERE dbo.tbl_InArchDate = #dd-mm-yyyy# Result : none.....

Does anyone see's the problem, I'm not..

Thank you for you kind suppport, it's very much appreciated.

[colintis]

A suggestion would be make a linked table from the SQL server and work your query with it to extract data.

For example, you have created a linked table from the server and it named tbl_InArch. Then in your query it will be this without dbo.

SELECT *
FROM tbl_InArch
WHERE tbl_InArchDate = #<the dates># 

Then for the date parameter, if you are running the query directly, you should be replace a field instead, so it would be something like this:

WHERE tbl_InArchDate = [Date]

If you are implementing through VBA, this is what most likely you are doing:

Dim InputDate As DateTime 'This is your date input from somewhere in the form
 
Dim strSQL As String
strSQL = "SELECT * FROM tbl_InArch WHERE tbl_InArch = #" & Format(InputDate, "mm/dd/yyyy") & "#"

A further explain, the format mm/dd/yyyy is SQL's standard date format, and the format function will do the work to change the date format from user input.

Finally, I also notice that the naming of tbl_InArchDate is a bit weired, just make sure it is a part of the field within the table tbl_InArchDate.