Querying a SQL Server using a date on a Form (AccessXP/SQL Server 2000)

Topic

New Reply

I have a Form (not a data access page) in an Access project that contains a date field. I want to use the value of that date field in a SQL string that will be passed to a SQL Server 2000 database. I cannot seem to get the portion of the query containing the date parameter to work.

Here is the SQL string:

strSQLUnitsInspected = “SELECT ProjectUnitAddress ” & _
“FROM qryInspectionProjectUnits ” & _
“WHERE (ProjectID = ” & frmProjectInspectionReport.ProjectID & “)” & _
” AND (InspectionDate = CONVERT(DATETIME, ” & frmProjectInspectionReport.InspectionDate & “, 102))”

I keep getting one of two errors, either no records have been returned, or there is a syntax error near the #.

I know Access usually sandwiches dates in between # signs, so I am guessing that somehow I am not getting the date information properly formatted in the SQL string.

Thanks in advance for the help!

Reply | Quote

Replies

SQL Server expects dates to be between single quotes, a la ‘1/1/2003’

Reply | Quote

Hi Shane,

Thanks! That did the trick

Reply | Quote