Problems with *** qdf.SQL = strSQL

[ineedahelp]

I had this code working and for some reason it is blowing up at my line: qdf.SQL = strSQL. I am pulling my hair out...any help!!????


Private Sub comboExcelForVe ndor_AfterUpdat e()
On Error GoTo Err_MyProc
Dim dbs As DAO.Database, qdf As DAO.QueryDef, strSQL As String
Set dbs = CurrentDb
Dim strTableName, strClient, strExcelName, strDate As String
Dim myPos

strTableName = Forms!frmDailyC lient!comboExce lforVendor
myPos = InStr(1, strTableName, "_", vbTextCompare) - 1
strExcelName = Left(strTableNa me, myPos)
strDate = Right(strTableN ame, 7)
Set qdf = dbs.QueryDefs(" qryCreateExcel" )

strSQL = "SELECT TOP 20 " & strTableName & ".Symbol, " & strTableName & ".Cusip, Sum(" & strTableName & ".TotalQty) AS SumOfTotalQty " & _
"FROM " & strTableName & _
"GROUP BY " & strTableName & ".Symbol, " & strTableName & ".Cusip, " & strTableName & ".ML_DailyR ate " & _
"HAVING (((" & strTableName & ".ML_DailyR ate) < 4.5 Or (" & strTableName & ".ML_DailyR ate) = 4.5)) " & _
"ORDER BY Sum(" & strTableName & ".ML_SMV);"
qdf.SQL = strSQL
MsgBox ("After")
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "qryCreateExcel ", "c:\documen ts and settings\" & mUser & "My Documents\compa nyName\" & strExcelName & "\" & strExcelName & " Rate Request" & strDate & ".xls", True
MsgBox ("File successfully exported")
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
Exit Sub

Exit_MyProc:
qdf.Close
Set dbs = Nothing
Set qdf = Nothing
Exit Sub

Err_MyProc:
Resume Exit_MyProc

End Sub

[NeoPa]

I can't see anything wrong at first glance.
Can you say how it blows up? What's the error message?
Do you understand debugging in VBA?
We can probably find an answer quicker and easier if you do.
I'm sure we'll get there though, one way or another.

[nullgumby]

blowing up at my line: qdf.SQL = strSQL.

I don't immediately see anything wrong with it either...one thought though, there might be an error in your SQL that's throwing an internal error in Access--since the SQL isn't valid.

On the line that's blowing up, I'd check the SQL in the immediate window. Maybe try to paste it and save it in a new query window to see if Access has a problem with it.

[NeoPa]

After the

qdf.SQL = strSQL

line try putting the

qdf.Close

line. This should save away the changes made to the SQL in the query so when you invoke it it works with your new SQL.
If this doesn't work, try adding

Debug.Print strSQL

after the close line then Copy and Paste the results from the Immediate window (open or goto with Ctrl-G) into a new post (in this thread).

[ineedahelp]

Thank you for your help...I was missing a space in my SQL and a \ in my transferspreads heet statement!!! Aaaaaagggghhhhh h!

[NeoPa]

Not a problem - I often Debug.Print my SQL strings to ensure no little problems have been overlooked.