Exporting to access VBA Error 3061

[veejay]

Title should read : Exporting to EXCEL

While running the following code I run into a 3061 error (expected 2).
Trying to use the debug mode but it doesn't seems to be working.

Private Sub export2Excel_Click()
On Error GoTo SubError
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim strSQL As String
    Dim rs1 As DAO.Recordset
    Dim i As Integer
    
    Dim intCounterAVP As Integer
    Dim strAVP As Variant
 
    'Show user work is happening
    DoCmd.Hourglass (True)
 
    '*********************************************
    '              RETRIEVE DATA
    '*********************************************
    strSQL = "SELECT zRangeIndividuals.EXAMINER_S AS Examiner, zRangeIndividuals.[Files Reviewed], zRangeIndividuals.[Total error], zRangeIndividuals.[Files with error], zRangeIndividuals.serror, zRangeIndividuals.[310(a)], zRangeIndividuals.Errors, zRangeIndividuals.Formal, zRangeIndividuals.Clerical, zRangeIndividuals.[sError & 30(a) % ], zRangeIndividuals.[Overall Quality %], zRangeIndividuals.[Team Leaders] " _
    + "FROM zRangeIndividuals " _
    + "ORDER BY zRangeIndividuals.EXAMINER_S "
    
    'Execute query and populate recordset
    Set rs1 = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    'If no data, don't bother opening Excel, just quit
    If rs1.RecordCount = 0 Then
        MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
        GoTo SubExit
    End If
  
    '*********************************************
    '             BUILD SPREADSHEET
    '*********************************************
    Set xlApp = Excel.Application
   
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
 
    With xlSheet
        .Name = "Exams"
        .Cells.Font.Name = "Century"
        .Cells.Font.Size = 11
  
        'Set column widths
        .Columns("A").ColumnWidth = 13
        .Columns("B").ColumnWidth = 25
        .Columns("C").ColumnWidth = 10
        .Columns("D").ColumnWidth = 10
        .Columns("E").ColumnWidth = 10
        .Columns("F").ColumnWidth = 10
        .Columns("G").ColumnWidth = 10
        .Columns("H").ColumnWidth = 10
        .Columns("I").ColumnWidth = 10
        .Columns("J").ColumnWidth = 10
        .Columns("K").ColumnWidth = 10
        .Columns("L").ColumnWidth = 10
        
      
        For cols = 0 To rs1.Fields.Count - 1
            .Cells(1, cols + 1).Value = rs1.Fields(cols).Name
        Next
 
        'Copy data from recordset to sheet
        .Range("A2").CopyFromRecordset rs1
       
    End With
 
SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    rs1.Close
    Set rs1 = Nothing
    Exit Sub
   
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
End Sub

[Minty]

Two things where are you getting the error in your code?

And really you have a field called [sError & 30(a) % ] in your query?
That is asking for issues like you wouldn't believe, the & % () are all symbols used for a variety of things in access and if they aren't causing you problems yet they will in the future.

[veejay]

I tried to use the Debug function but I believe I might be using it wrongly as it's not highlighting anything.

I did have a field called [sError & 30(a) % ] but i've updated all () and % and & and now this part looks like:

    strSQL = "SELECT zRangeIndividuals.Examiner, zRangeIndividuals.[Files Reviewed], " _
    + "zRangeIndividuals.[Total discrepancies], zRangeIndividuals.[Files with discrepancy], " _
    + "zRangeIndividuals.Substantive, zRangeIndividuals.[30a], zRangeIndividuals.Nice, zRangeIndividuals.Formal, " _
    + "zRangeIndividuals.Clerical, zRangeIndividuals.[Substantive and 30a], " _
    + "zRangeIndividuals.[Overall Quality], zRangeIndividuals.Manager " _
    + "FROM zRangeIndividuals"

I still get the 3061 error.
I'm 100% certain that the field are correctly spelled as I (shamelessly) used the design mode for my query then looked at the SQL.

[Micron]

You put a break point (in this case, probably at the first line) and cause the procedure to execute - be that a form button or whatever your trigger is. When code stops at the break point line, F8 through it (each F8 press executes a line) and see when/where the code fails.

Likely a field reference isn't being resolved. The builder is no guarantee because there are times when you have to know that a parent reference/object is required before some other one that you're able to pick. So the names might be correct, but their "address" (reference) isn't. Note that when code breaks on a line, that line has not been executed, so any variables for example, have not been dealt with. Suggest that if the problem is the execution of your sql statement that you insert Debug.Print before Set rs1...

If the sql output doesn't look correct in the immediate window, you might figure out the problem. If it does, you copy/paste the sql into a new query window and attempt to run it. Access might highlight the problem part in sql view for you. If you're able to do all that but don't get anywhere, maybe posting the immediate window output will give us a clue. BTW, I would not use + to join sql portions together. The proper character is the ampersand (& ) to perform concatenation. + is for addition.

[ssanfu]

Another point:

And really you have a field called [sError & 30(a) % ] in your query?
That is asking for issues like you wouldn't believe, the & % () are all symbols used for a variety of things in access and if they aren't causing you problems yet they will in the future.

You should remove any and all spaces in object names: this includes field, table, form, query and report names.

[June7]

The + character will concatenate strings but if any inputs are Null, the result will be Null. This can be useful in some situations. The & basically treats Null as an empty string.