none
Runtime Error 3075

    Question

  • Hi

    I am getting the following error:

    Runtime error 3075 - Syntax error (missing operator) in query expresseion

    from the following code:

    Private Sub AddNew_Report_Click()
    Dim strSQL As String
    
    ' SQL code to add new record
    strSQL = "INSERT INTO tblReports ([attDate], [Period1], " & _
        "[User ID], [Pupil ID], [Attendance], [Behaviour], [Homework]) " & _
            "VALUES (#" & Format(Date1, "mm\/dd\/yyyy") & "#, " & _
            cmbPeriod & ", " & Text74 & ", " & Text231 & ", " & _
            Attendance1 & ", " & Behaviour1 & ", " & Homework1 & ") "
    
    ' Check there is a value in all fields
    If Len(Teacher1) > 0 And Len(Pupil1) > 0 And Len(cmbPeriod) >= 1 And Len(cmbPeriod) <= 6 And Len(Date1) > 0 And Len(Attendance1) >= 1 And Len(Attendance1) <= 3 And Len(Behaviour1) >= 1 And Len(Behaviour1) <= 4 And Len(Homework1) >= 1 And Len(Homework1) <= 3 Then
        ' Execute SQL code
         DoCmd.RunSQL strSQL
            ' Display message
            MsgBox ("The report was successfully generated"), vbInformation, "School Monitoring System"
        ' Close current form and open switchboard
        DoCmd.Close
        DoCmd.OpenForm "frmTeacher"
        ' If one or more fields are blank, error message
        Else
        MsgBox ("Please enter all fields"), vbInformation, "School Monitoring System"
    
    End If
    
    End Sub
    

    Can anyone advise me on this

    Paul


    PaulMoscrop
    Thursday, November 24, 2011 4:06 PM

Answers

  • You might have to step to the next line of code before the variable value is set. At that point you can debug.

    Just a thought.


    Eric Williams
    • Marked as answer by Bruce Song Wednesday, December 07, 2011 10:46 AM
    Thursday, November 24, 2011 4:46 PM

All replies

  • I debug this as follows:

    Set a breakpoint on the RunSQL line.

    In the Immediate window, enter "? strSQL" and hit Enter.

    Paste the resulting string in a new query in SQL view.

    Switch to design view.

    The parser will point out where the error is.

     

    Really, Text74, Text231?


    -Tom. Microsoft Access MVP
    Thursday, November 24, 2011 4:09 PM
  • I have got as far as adding the breakpoint however when I open the Immediate Window and enter "? strSQL" nothing is returned. 

    I know my naming conventions are not currently "suitable" but these will be changed. 


    PaulMoscrop
    Thursday, November 24, 2011 4:14 PM
  • You did not include the double-quotes, did you? And you have a space between ? and strSQL? Then it should return your SQL string or you have much bigger problems.

    You can also find the value of any variable in the Locals window, under the View menu.


    -Tom. Microsoft Access MVP
    Thursday, November 24, 2011 4:20 PM
  • My breakpoint is on the line 
    DoCmd.RunSQL strSQL
    
    In the immediate window, if I type ? strSQL, nothing is returned. If I type "strSQL" I get a compile error.
    PaulMoscrop
    Thursday, November 24, 2011 4:25 PM
  • You might have to step to the next line of code before the variable value is set. At that point you can debug.

    Just a thought.


    Eric Williams
    • Marked as answer by Bruce Song Wednesday, December 07, 2011 10:46 AM
    Thursday, November 24, 2011 4:46 PM