What is the correct sql syntax for dates in access vba

Question

I've written the following SQL string in access vba but it fails in access with

Error 3075 syntax error missing operator

I've used debug.print to capture the SQL and then run it in SSMS and I then get 'Operand type clash: date is incompatible with int'.
I have used Allen Brown's SQLDate function to try and ensure my dates are in the correct format, but this isn't working.

It executes without error in SSMS if I enclose the dates in single quotes. I added single quotes to the string(either side of each of the uses of SQLDate) but it still fails. I can't figure out how to edit my string so that the date is passed through in the correct format.

The data type of StartDate and EndDate is date.

strSQLDeps = "UPDATE dbo_Production_Schedule_1 " & _
            " SET dbo_Production_Schedule_1.StartDate = " & SQLDate(!EndDate) & _
            " FROM (dbo_Production_Schedule INNER JOIN dbo_Production_Sched_Dependencies " & _
            " ON dbo_Production_Schedule.ProductionScheduleID = dbo_Production_Sched_Dependencies.ProdSchedID)" & _
            " INNER JOIN dbo_Production_Schedule AS dbo_Production_Schedule_1" & _
            " ON (dbo_Production_Sched_Dependencies.DependentActivityID = dbo_Production_Schedule_1.ActivityID)" & _
            " AND (dbo_Production_Schedule.ItemSerialNo = dbo_Production_Schedule_1.ItemSerialNo)" & _
            " WHERE dbo_Production_Schedule_1.StartDate < " & SQLDate(!EndDate) & _
            " AND dbo_Production_Schedule.ProductionScheduleID = " & !ProductionScheduleID & ";"

This is the debug.print -

UPDATE dbo_Production_Schedule_1  
SET dbo_Production_Schedule_1.StartDate = #02/12/2018#
FROM (dbo_Production_Schedule INNER JOIN dbo_Production_Sched_Dependencies  
ON dbo_Production_Schedule.ProductionScheduleID = dbo_Production_Sched_Dependencies.ProdSchedID) 
INNER JOIN dbo_Production_Schedule AS dbo_Production_Schedule_1 
ON (dbo_Production_Sched_Dependencies.DependentActivityID = dbo_Production_Schedule_1.ActivityID) 
AND (dbo_Production_Schedule.ItemSerialNo = dbo_Production_Schedule_1.ItemSerialNo) 
WHERE dbo_Production_Schedule_1.StartDate < #02/12/2018# 
AND dbo_Production_Schedule.ProductionScheduleID = 5923;

Instead of building a string and executing you should use parameterized queries or stored procedures. Then you don't have to fight this. — Sean Lange
Thanks Sean - I will try the parameterized query approach - I already have a select query for the bulk of this, but hadn't thought to create an update version of it. I'm very new to both access and sql so I'm learning every day! — JennyW
Thanks @SeanLange - the parametrized query is working perfectly. — JennyW
Glad you got that working. The biggest upside is you have found a better approach to your queries that not only avoids hassles like you were fighting but also protects you against sql injection. Well done!! — Sean Lange
you could always use ISO formats like "'" & Format(!endDate, "yyyy-mm-dd") & "'" — Krish

Situation Situation · Accepted Answer · 2018-01-25T15:44:26

0

votes

Use the convert function like this:

Convert('Date_String',SQL_DATE)

What is the correct sql syntax for dates in access vba

1 Answers