Error when trying to export from table to Excel

[tobinjames]

I have some VBA code that sits behind a button on a form. The code runs a query to insert results into a temporary table, export the data from the table to an Excel file with a defined worksheet name, and then delete the temporary table. The query itself uses parameters that are provided by the user from the form prior to them clicking on the button. When I click the button, I receive the following error: "Too few parameters. Expected 1."

The VBA code I am using is as follows:

Private Sub Command34_Click()
On Error GoTo Err_Command34_Click

Dim strSQL As String
Dim strTable As String
Dim strElementTable As String
Dim strRemLifeFrom As String
Dim strRemLifeTo As String
Dim strContractor As String
Dim strExcelFile As String
Dim strWorkSheet As String

strTable = "tblTemp"


strElementTable = [Forms]![PROGFORM]![ElementTable]
strRemLifeFrom = [Forms]![PROGFORM]![RemLifeFrom]
strRemLifeTo = [Forms]![PROGFORM]![RemLifeTo]
strContractor = [Forms]![PROGFORM]![Contractor]
strExcelFile = "C:\Temp\ProgrammeExport\"
strExcelFile = strExcelFile & strContractor & "_"
strWorkSheet = strElementTable

strSQL = "SELECT WorkPlanner.UPRN, WorkPlanner.[Block Ref], WorkPlanner.[Flat Number], WorkPlanner.[Block/House name], WorkPlanner.[House Number], WorkPlanner.[Street 1], WorkPlanner.[Street 2], WorkPlanner.Locality, WorkPlanner.Town, WorkPlanner.Postcode, "
strSQL = strSQL & strElementTable & ".[Rem Life], " & strElementTable & ".[Rem Life Year], " & strElementTable & ".[Year Programmed] "
strSQL = strSQL & "INTO " & strTable
strSQL = strSQL & " FROM " & strElementTable & " INNER JOIN WorkPlanner ON " & strElementTable & ".UPRN = WorkPlanner.UPRN "
strSQL = strSQL & "WHERE WorkPlanner.UPRN IN (SELECT UPRN FROM " & strElementTable & " WHERE (Contractor = " & strContractor & " AND ([Rem Life] BETWEEN " & strRemLifeFrom & " AND " & strRemLifeTo & ")))"

CurrentDb.Execute strSQL

DoCmd.TransferSpreadsheet acExport, 8, strTable, strExcelFile, True, strWorkSheet

DoCmd.DeleteObject acTable, strTable

Exit_Command34_Click:
Exit Sub

Err_Command34_Click:
MsgBox Err.Description
Resume Exit_Command34_Click

End Sub


I'm not sure what this error means or what part of my code is failing.

[boblarson]

Is Contractor text or numeric? If text you need quotes. Also, is Rem Life a date? If so, you need octothorpes (#).

Also, for this code:

strExcelFile = strExcelFile & strContractor & "_"

you need to provide the file extention if it isn't already there in strContractor, which I doubt it would be:

strExcelFile = strExcelFile & strContractor & "xls"

[tobinjames]

Hi Bob,

Thanks for your advice. I realised once I had posted the code that I had missed out the file extension.

The Contractor value is text, but I'm not sure where I need to put the quotes and whether they should be single or double quotes. Sorry for seeming a bit thick, but could you advise? Rem Life is a numeric value so I'm assuming this is ok as it is?

[boblarson]

The quotes for Contractor would be like this:

strSQL = strSQL & "WHERE WorkPlanner.UPRN IN (SELECT UPRN FROM " & strElementTable & " WHERE (Contractor = " & Chr(34) & strContractor & Chr(34) & " AND ([Rem Life] BETWEEN " & strRemLifeFrom & " AND " & strRemLifeTo & ")))"


I like to use Chr(34) which is a double quote because it is easier to read than three double quotes and/or using single quotes which can be a problem for things like Bob's Contracting where the name has a single quote in it.