Syntax Error (Access 97)

Topic

New Reply

In the attached database, I have a form “frmDataEntry”. I have taken this form from another db and am attempting to modify it to use in this one. I am getting syntax error 3075, missing operator, when I click on the cmdAction button on that form. I have looked through to see if I have any typos and couldn’t find any the 10 times that I looked. Would someone mind taking a look at this and let me know where I am going wrong. It happens when I either try to add a new record or update an existing one.

Thanks a bunch!!

Reply | Quote

Replies

You have a lot of quotes that where missing in the line that define sSQL. All fields that are Text fields have to be surrounded by quotes.
This corrected line should do it :

sSQL = "INSERT INTO tblSample (SDate,AssocName,QARep,Manager,Site,Department," & _
   "ARN,ARNName,Function,ErrorType,ErrWgt,QANotes) " & _
   "VALUES (#" & Me.txtSdate & "#, '" & Me.cboAssocName & "', '" & _
   Me.cboQARep & "', '" & Me.cboManager & "', '" & Me.cboSite & "', '" & _
   Me.cboDepartment & "', '" & Me.txtARN & "', '" & Me.txtARNName & "', '" & _
   Me.cboFunction & "', '" & Me.cboErrorType & "', " & Me.txtErrWgt & ", '" & _
   Me.txtQANotes & "')"

Reply | Quote

Thanks Francois!!

That took care of it. Is that also the reason why I am having the problem with the “Update” action for the same command button?

Case “&Update”
sSQL = “UPDATE tblSample SET SDate=#” & Me.txtSdate & “#, ” & _
“AssocName='” & Me.cboAssocName & “‘, ” & _
“QARep='” & Me.cboQARep & “‘, ” & _
“Manager='” & Me.cboManager & “‘, ” & _
“Site='” & Me.cboSite & “‘, ” & _
“Department='” & Me.cboDepartment & “‘,” & _
“Arn=” & Me.txtARN & “,” & _
“ARNName=” & Me.txtARNName & “,” & _
“Function='” & Me.cboFunction & “‘, ” & _
“ErrorType='” & Me.cboErrorType & “‘, ” & _
“ErrWeight=” & Me.txtErrWgt & “, ” & _
“QANotes=” & Me.txtQANotes & “, ” & _
“WHERE SampleID = ” & Me.lstData & “;”
Debug.Print sSQL
CurrentDb.Execute sSQL
Call cmdClear_Click

Reply | Quote

As far as I can see, the fields Arn, ARNName and QANotes are Text fields.
These should be surrounded by quotes.

Case "&Update"
sSQL = "UPDATE tblSample SET SDate=#" & Me.txtSdate & "#, " & _
"AssocName='" & Me.cboAssocName & "', " & _
"QARep='" & Me.cboQARep & "', " & _
"Manager='" & Me.cboManager & "', " & _
"Site='" & Me.cboSite & "', " & _
"Department='" & Me.cboDepartment & "'," & _
"Arn= '" & Me.txtARN & "'," & _
"ARNName= '" & Me.txtARNName & "'," & _
"Function='" & Me.cboFunction & "', " & _
"ErrorType='" & Me.cboErrorType & "', " & _
"ErrWeight=" & Me.txtErrWgt & ", " & _
"QANotes= '" & Me.txtQANotes & "', " & _
"WHERE SampleID = " & Me.lstData & ";"
Debug.Print sSQL
CurrentDb.Execute sSQL
Call cmdClear_Click

This is not tested, as I don’t find how you update in the form.

Reply | Quote

Ok, I found how to test.

You have also to remove the comma after the field QANotes.

"QANotes= '" & Me.txtQANotes & "' " & _

Reply | Quote

Thanks so much for your help with this!!!

Reply | Quote

Francois,

The field “QANotes” does not have to have a value, and the code (in it’s current state) will not let me add or update a record unless there is a value in txtQANotes. I tried to add the following snipit of code, but this did not fair too well:

If IsNull(Me.txtQANotes) Then
sSQL = sSQL & “QANotes=Null, ”
Else
sSQL = sSQL & “QANotes='” & Me.txtQANotes.Value & “‘, ”
End If

Any suggestions?

Thanks again!!

Reply | Quote

If QANotes is still the last field in the SQL statement, there shouldn’t be a comma after it, as mentioned by Francois:

If IsNull(Me.txtQANotes) Then
sSQL = sSQL & "QANotes=Null "
Else
sSQL = sSQL & "QANotes='" & Me.txtQANotes.Value & "' "
End If

Reply | Quote

Hans,

Thanks for the help, I have made the necessary adjustments to the “UPDATE” statement and it works great. I have the following code for the “INSERT” statement and am not sure how to add that the If IsNull statement to this.

sSQL = “INSERT INTO tblSample (SDate,AssocName,QARep,Manager,Site,Department,” & _
“ARN,ARNName,Function,ErrorType,ErrWgt,QANotes) ” & _
“VALUES (#” & Me.txtSdate & “#, ‘” & Me.cboAssocName & “‘, ‘” & _
Me.cboQARep & “‘, ‘” & Me.cboManager & “‘, ‘” & Me.cboSite & “‘, ‘” & _
Me.cboDepartment & “‘, ‘” & Me.txtARN & “‘, ‘” & Me.txtARNName & “‘, ‘” & _
Me.cboFunction & “‘, ‘” & Me.cboErrorType & “‘, ” & Me.txtErrWgt & “, ‘” & _
Me.txtQANotes & “‘)”

Thanks for your help

Reply | Quote

Marie,

Try this:

sSQL = “INSERT INTO tblSample (SDate,AssocName,QARep,Manager,Site,Department,” & _
“ARN,ARNName,Function,ErrorType,ErrWgt,QANotes) ” & _
“VALUES (#” & Me.txtSdate & “#, ‘” & Me.cboAssocName & “‘, ‘” & _
Me.cboQARep & “‘, ‘” & Me.cboManager & “‘, ‘” & Me.cboSite & “‘, ‘” & _
Me.cboDepartment & “‘, ‘” & Me.txtARN & “‘, ‘” & Me.txtARNName & “‘, ‘” & _
Me.cboFunction & “‘, ‘” & Me.cboErrorType & “‘, ” & Me.txtErrWgt & “, ‘”

If IsNull(Me.txtQANotes) Then
sSQL = sQSL & “Null)”
Else
sSQL = sSQL & Me.txtQANotes & “‘)”
End If

Reply | Quote

Hans,

I am getting syntax error 3075. Syntax error in string in query expression “Null)’

Reply | Quote

Oops, my fault. I messed up the position of the quotes. Sorry about that!

sSQL = "INSERT INTO tblSample (SDate,AssocName,QARep,Manager,Site,Department," & _
"ARN,ARNName,Function,ErrorType,ErrWgt,QANotes) " & _
"VALUES (#" & Me.txtSdate & "#, '" & Me.cboAssocName & "', '" & _
Me.cboQARep & "', '" & Me.cboManager & "', '" & Me.cboSite & "', '" & _
Me.cboDepartment & "', '" & Me.txtARN & "', '" & Me.txtARNName & "', '" & _
Me.cboFunction & "', '" & Me.cboErrorType & "', " & Me.txtErrWgt & ", "

If IsNull(Me.txtQANotes) Then
sSQL = sSQL & "Null)"
Else
sSQL = sSQL & "'" & Me.txtQANotes & "')"
End If

Reply | Quote

Once again thanks for sharing your infinite wisdom with me!! It works perfectly. You are fabulous!!!

Reply | Quote

Hans,

Certain puncuation seems to be causing Run-Time Error 3075. Specifically the “apostrophe”. Any thoughts as to why this is happening?

Thanks!

Reply | Quote

That is logical. You are constructing an SQL string in code. This string is delimited by double quotes ". The string values within this SQL string are delimited by single quotes (apostrophes) '. If a string value contains an apostrophe, this is seen as a string delimiter, and hence it throws execution off. If you have values containing apostrophes, but none containing double quotes, you can get around it as follows:

sSQL = "INSERT INTO tblSample (SDate,AssocName,QARep,Manager,Site,Department," & _
"ARN,ARNName,Function,ErrorType,ErrWgt,QANotes) " & _
"VALUES (#" & Me.txtSdate & "#, " & Chr(34) & Me.cboAssocName & Chr(34) & ", " & _
Chr(34) & Me.cboQARep & Chr(34 & ", " & Chr(34) & Me.cboManager & Chr(34) & ", " & _
Chr(34) & Me.cboSite & Chr(34) & ", " & Chr(34) & Me.cboDepartment & Chr(34) & ", " & _
Chr(34) & Me.txtARN & Chr(34) & ", " & Chr(34) & Me.txtARNName & Chr(34) & ", " & _
Chr(34) & Me.cboFunction & Chr(34) & ", " & Chr(34) & Me.cboErrorType & Chr(34) & ", " & _
Chr(34) & Me.txtErrWgt & Chr(34) & ", "

If IsNull(Me.txtQANotes) Then
sSQL = sSQL & "Null)"
Else
sSQL = sSQL & Chr(34) & Me.txtQANotes & Chr(34) & ")"
End If

Chr(34) is the double quote “.

Reply | Quote