OK -- my apologies, in all the effort to get the TransferSpreadsheet action
properly coded, I forgot that you originally were trying to filter a report
and then export that report.
What we will need to do is create a temporary query that is properly
filtered, and then we'll export that query.
Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strQuery As String 'Name of query to export.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for query.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CUSTOMER_NAME] IN (" & Left$(strWhere2, lngLen)
& ")"
End If
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
strQuery = "qryAvgCostMile"
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query with WHERE filter
' that will be used for exporting data
strSQL = "SELECT * FROM [" & strQuery & "] WHERE " & _
strWhere3 & ";"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQName, "C:\" & "AvgCostMile" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
Err.Clear
On Error Resume Next
qdf.Close
Set qdf = Nothing
dbs.QueryDefs.Delete strQName
dbs.Close
Set dbs = Nothing
End Sub
--
Ken Snell
<MS ACCESS MVP>
Adam said:
OK - Let's change that code section to this so that you stop before you
get
to the error handler:
Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
OK - now the problem appears to be in your query "qryAvgCostMile". Post
the
SQL statement of this query and let's see what it's supposed to be doing.
--
Ken Snell
<MS ACCESS MVP>
- Show quoted text -
Should it be referencing the list box (lstCustName) selections or the
query? I would think both, but I don't see where it's looking at the
list box. Here is the SQL:
SELECT ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
Count(ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE) AS Frequency,
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_FREIGHT_PAID) AS [Freight Cost],
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_MILEAGE) AS Miles, [Miles]/[Freight
Cost] AS [Avg Cost/Mile]
FROM ODSPROD_T_TSI_FBSUM2
GROUP BY ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS
HAVING (((ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Approv*" Or
(ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Pai*"))
ORDER BY ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME;
