[RESOLVED] Run-Time Error '3075': Syntax Error (missing Operator) MS Access/VBA/SQL

[Eclectic211]

Noob here, I'm making an application that selects a piece of equipment from a list, the user answers a number of questions from a drop down list, then hits save to record who completed the survey and the date and time. I've only got one question loaded at the moment as I was trying to get the basics working then add questions to it.

I'm getting an error 3075 on the dbs.Execute (sqlStmt) line. The sqlStmt string above it looks right to me but I'm a noob. I put a break in the code and all the variable values look good, I had a type mismatch previously that I resolved.

Hoping the brain pool here can help me out.

Thanks,

Private Sub QASurveyUpdate_Click()
Dim MyPersonID As Integer
Dim FireAlarmTrackerID As Double

If IsNull(CboPerson.Value) Then
    MsgBox ("Please Select a user ID")
    Else
    MyPersonID = CboPerson.Value
    FireAlarmTrackerID = lstNotification.Column(6, lstNotification.ListIndex + 1)
    sqlStmt = "UPDATE QANotification SET peopleID=" & MyPersonID & ",LastModified='" & Now() & ",QuestionID90='" & QuestionID1.Value & "' WHERE FireAlarmTrackerID=" & FireAlarmTrackerID
Call dbAction("open")
dbs.Execute (sqlStmt)

End If

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.


Take a closer look at the value of sqlStmt after you put the values in... you have a ' character before one of the values, but not after.

Note that the ' character isn't the thing to use for Date values, and you will (sometimes) get issues with the kind of thing you have done... For an explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[wes4dbt]

Don't know what data type your database fields are so I'm just guessing but this definitely doesn't look right,

LastModified='" & Now() & ",QuestionID90

Shouldn't it be

LastModified='" & Now() & "',QuestionID90

you should put a break point here "dbs.Execute (sqlStmt)" and check the value of "sqlStmt", it will make it easier to spot mistakes. That being said, you really should be using parameters in your SQL statement.

[Eclectic211]

Thank you Man, that took care of it. Need to work on my punctuation apparently.

[Eclectic211]

Thank you for pointing out the parameters I didn't realize it was an option to do it this way. I'd only seen it done with string concatenation.