Export using strSQL

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I am trying to export using a SELECT statment and if returns "Too few
parameters, expected 4". I am pretty sure the SELECT statment is ok. I have
tested it in a query. What am I doing wrong.

Thank you,

Steven

Sub Export()
On Error GoTo ExportBatch_Err

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim strSQL As String

strSQL = "SELECT [SM&R].Co, [SM&R].Account, Sum([SM&R].Amount) AS
SumOfAmount, ChartOfAccounts.FSCategory " + _
"FROM [SM&R] LEFT JOIN ChartOfAccounts ON ([SM&R].Account =
ChartOfAccounts.Account) AND ([SM&R].Co = ChartOfAccounts.Co) " + _
"WHERE ((([SM&R].Period) <= [Forms]![FLeadLookup]![Combo42])) " + _
"GROUP BY [SM&R].Co, [SM&R].Account, ChartOfAccounts.FSCategory " + _
"HAVING ((([SM&R].Co)=[Forms]![FLeadLookup]![Co]) AND
(([SM&R].Account) Between [Forms]![FLeadLookup]![Text0] And
[Forms]![FLeadLookup]![Combo19]) AND ((Sum([SM&R].Amount))<>0)) " + _
"ORDER BY [SM&R].Co, [SM&R].Account"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If Dir("C:\My Documents\AcctBalances.xls") = "" Then
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctBalances.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
Else
If IsOpen("C:\My Documents\AcctBalances.xls") Then
MsgBox "File C:\My Documents\AcctBalances.xls is open." + Chr(13) +
Chr(13) + _
" Please close the file and try again."
Exit Sub
Else
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctExport.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
End If
End If

ExportBatch_Exit:
Exit Sub

ExportBatch_Err:
MsgBox Error$
Resume ExportBatch_Exit
End Sub
 
Hi Steven

When you open a saved query from the Database window, the Access application
resolves any references to Access objects such as controls on forms etc.

However, when you use DAO.Database.OpenRecordset on a query or a SQL string,
Access is bypassed and the request is passed directly by DAO to the database
engine (Jet) which knows nothing of Access forms.

Therefore, every instance of [Forms]![FLeadLookup]![...] in your query
results in an unresolved parameter.

This situation is usually resolved by concatenating the actual values (not
the names of the controls) into the SQL string. For example:

"WHERE ((([SM&R].Period) <= " & [Forms]![FLeadLookup]![Combo42] & ")) " &
_

[Note the use of "&" instead of "+" for string concatenation. "+" can
produce unexpected results]

However, there is another problem with your code, and that is that
TransferSpreadsheet works only on named tables or queries, not on Recordset
objects, so:
DoCmd.TransferSpreadsheet acExport, 8, rs, ...
will not work.

I suggest you save your SQL in a named query and then use that for the
export:

DoCmd.TransferSpreadsheet acExport, 8, "qryExportAccountBalances", ...

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Steven said:
I am trying to export using a SELECT statment and if returns "Too few
parameters, expected 4". I am pretty sure the SELECT statment is ok. I
have
tested it in a query. What am I doing wrong.

Thank you,

Steven

Sub Export()
On Error GoTo ExportBatch_Err

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim strSQL As String

strSQL = "SELECT [SM&R].Co, [SM&R].Account, Sum([SM&R].Amount) AS
SumOfAmount, ChartOfAccounts.FSCategory " + _
"FROM [SM&R] LEFT JOIN ChartOfAccounts ON ([SM&R].Account =
ChartOfAccounts.Account) AND ([SM&R].Co = ChartOfAccounts.Co) " + _
"WHERE ((([SM&R].Period) <= [Forms]![FLeadLookup]![Combo42])) " +
_
"GROUP BY [SM&R].Co, [SM&R].Account, ChartOfAccounts.FSCategory "
+ _
"HAVING ((([SM&R].Co)=[Forms]![FLeadLookup]![Co]) AND
(([SM&R].Account) Between [Forms]![FLeadLookup]![Text0] And
[Forms]![FLeadLookup]![Combo19]) AND ((Sum([SM&R].Amount))<>0)) " + _
"ORDER BY [SM&R].Co, [SM&R].Account"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If Dir("C:\My Documents\AcctBalances.xls") = "" Then
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctBalances.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
Else
If IsOpen("C:\My Documents\AcctBalances.xls") Then
MsgBox "File C:\My Documents\AcctBalances.xls is open." + Chr(13) +
Chr(13) + _
" Please close the file and try again."
Exit Sub
Else
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctExport.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
End If
End If

ExportBatch_Exit:
Exit Sub

ExportBatch_Err:
MsgBox Error$
Resume ExportBatch_Exit
End Sub
Click to expand...
 
Thank you, I was able to get the recordset to open per your help. The other
part, ok, I was hoping it worked with a strSQL.

Graham Mandeno said:
Hi Steven

When you open a saved query from the Database window, the Access application
resolves any references to Access objects such as controls on forms etc.

However, when you use DAO.Database.OpenRecordset on a query or a SQL string,
Access is bypassed and the request is passed directly by DAO to the database
engine (Jet) which knows nothing of Access forms.

Therefore, every instance of [Forms]![FLeadLookup]![...] in your query
results in an unresolved parameter.

This situation is usually resolved by concatenating the actual values (not
the names of the controls) into the SQL string. For example:

"WHERE ((([SM&R].Period) <= " & [Forms]![FLeadLookup]![Combo42] & ")) " &
_

[Note the use of "&" instead of "+" for string concatenation. "+" can
produce unexpected results]

However, there is another problem with your code, and that is that
TransferSpreadsheet works only on named tables or queries, not on Recordset
objects, so:
DoCmd.TransferSpreadsheet acExport, 8, rs, ...
will not work.

I suggest you save your SQL in a named query and then use that for the
export:

DoCmd.TransferSpreadsheet acExport, 8, "qryExportAccountBalances", ...

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Steven said:
I am trying to export using a SELECT statment and if returns "Too few
parameters, expected 4". I am pretty sure the SELECT statment is ok. I
have
tested it in a query. What am I doing wrong.

Thank you,

Steven

Sub Export()
On Error GoTo ExportBatch_Err

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim strSQL As String

strSQL = "SELECT [SM&R].Co, [SM&R].Account, Sum([SM&R].Amount) AS
SumOfAmount, ChartOfAccounts.FSCategory " + _
"FROM [SM&R] LEFT JOIN ChartOfAccounts ON ([SM&R].Account =
ChartOfAccounts.Account) AND ([SM&R].Co = ChartOfAccounts.Co) " + _
"WHERE ((([SM&R].Period) <= [Forms]![FLeadLookup]![Combo42])) " +
_
"GROUP BY [SM&R].Co, [SM&R].Account, ChartOfAccounts.FSCategory "
+ _
"HAVING ((([SM&R].Co)=[Forms]![FLeadLookup]![Co]) AND
(([SM&R].Account) Between [Forms]![FLeadLookup]![Text0] And
[Forms]![FLeadLookup]![Combo19]) AND ((Sum([SM&R].Amount))<>0)) " + _
"ORDER BY [SM&R].Co, [SM&R].Account"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If Dir("C:\My Documents\AcctBalances.xls") = "" Then
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctBalances.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
Else
If IsOpen("C:\My Documents\AcctBalances.xls") Then
MsgBox "File C:\My Documents\AcctBalances.xls is open." + Chr(13) +
Chr(13) + _
" Please close the file and try again."
Exit Sub
Else
DoCmd.TransferSpreadsheet acExport, 8, rs, "C:\My
Documents\AcctExport.xls", True, ""
MsgBox "Exported to C:\My Documents\AcctBalances.xls"
End If
End If

ExportBatch_Exit:
Exit Sub

ExportBatch_Err:
MsgBox Error$
Resume ExportBatch_Exit
End Sub
Click to expand...
Click to expand...
 
Back
Top