Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Error (Access 97)

    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!!

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    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

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    As far as I can see, the fields Arn, ARNName and QANotes are Text fields.
    These should be surrounded by quotes.
    <pre>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</pre>


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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    Ok, I found how to test.

    You have also to remove the comma after the field QANotes.
    <pre>"QANotes= '" & Me.txtQANotes & "' " & _</pre>

    Francois

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    Thanks so much for your help with this!!!

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    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!!

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    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 :
    <pre>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 & "')"</pre>

    Francois

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Error (Access 97)

    If QANotes is still the last field in the SQL statement, there shouldn't be a comma after it, as mentioned by Francois:
    <code>
    If IsNull(Me.txtQANotes) Then
    sSQL = sSQL & "QANotes=Null "
    Else
    sSQL = sSQL & "QANotes='" & Me.txtQANotes.Value & "' "
    End If</code>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Error (Access 97)

    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

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    Hans,

    I am getting syntax error 3075. Syntax error in string in query expression "Null)'

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Error (Access 97)

    Oops, my fault. I messed up the position of the quotes. Sorry about that!
    <code>
    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</code>

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

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

  14. #14
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (Access 97)

    Hans,

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

    Thanks!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Error (Access 97)

    That is logical. You are constructing an SQL string in code. This string is delimited by double quotes <code>"</code>. The string values within this SQL string are delimited by single quotes (apostrophes) <code>'</code>. 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:
    <code>
    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
    </code>
    Chr(34) is the double quote ".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •