I would like some help with the proper way to code a subquery. My strSQL is the first query. It aggregates data for me and selects the top 20. After this is done, I would like to SORT this output on symbol. I would also like to ADD a field to this query that I could run the UPDATE query on to add the date. Can I also programatically change the field name of a query using CAPTION?
Thank you for any help!!
Private Sub comboExcelForVendor_AfterUpdate()
On Error GoTo Err_MyProc
Dim dbs As DAO.Database, qdf As DAO.QueryDef, strSQL, strSQLFinal As String
Set dbs = CurrentDb
Dim strTableName, strClient, strExcelName, strDate As String
Dim myPos
strTableName = Forms!frmDailyClient!comboExcelforVendor
myPos = InStr(1, strTableName, "_", vbTextCompare) - 1
strExcelName = Left(strTableName, myPos)
strDate = Right(strTableName, 7)
Set qdf = dbs.QueryDefs("qryVendorRequest")
strSQL = "SELECT TOP 20 " & strTableName & ".Symbol, " & strTableName & ".Cusip, Sum(" & strTableName & ".TotalQty) AS SumOfTotalQty " & _
"FROM " & strTableName & _
" GROUP BY " & strTableName & ".Symbol, " & strTableName & ".Cusip, " & strTableName & ".ML_DailyRate " & _
"HAVING (((" & strTableName & ".ML_DailyRate) < 4.5 Or (" & strTableName & ".ML_DailyRate) = 4.5)) " & _
"ORDER BY Sum(" & strTableName & ".ML_SMV);"
qdf.SQL = strSQL
strSQLFinal = "SELECT strSQL.Symbol, strSQL.Cusip, strSQL.SumOfTotalQty " & _
"FROM strSQL " & _
"ORDER BY strSQL.Symbol;"
qdf.SQL = strSQLFinal
Debug.Print strSQLFinal
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryVendorRequest", "c:\documents and settings\" & mUser & "\My Documents\COMPANY NAME\" & 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