Error of query exporting

[drunkenneo]

Hi My code is:

Private Sub btPending_Click()
Dim penddate As Date
Dim a, str As String
Dim qdfTemp As DAO.QueryDef
Dim strQDF As String
If (CStr(Format(Now(), "dddd")) <> "Tuesday" Or CStr(Format(Now(), "dddd")) <> "Thursday") Then
If MsgBox("Today is not Tuesday/Thursday" & vbLf & "Do you Still want to continue", vbYesNo) = vbNo Then
Exit Sub
End If
End If

penddate = PlusWorkdays(Format("11/12/2013", "dd/mm/yyyy"), -10)
'To Do

str = " ...... " & _
"WHERE (((orderInvoice.dateRecieved)<#" & penddate & " # ) AND ((partnerBiWeekly.Description)=" & Chr(34) & "Daily" & Chr(34) & ") AND ((orderList.deliveredDate)<> " & Chr(34) & "Cancelled" & Chr(34) & ") AND ((orderList.deliveredDate) Is Null))"

Debug.Print str

Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, str)
qdfTemp.Close


Set qdfTemp = Nothing

a = Format(Now(), "dd.mm.yyyy")

'DoCmd.TransferSpreadsheet , acExport, acTypespreadsheetTypeExcel9, strQDF, "C:\PendingOrders_" & a & ".xls", False

'DoCmd.OutputTo acQuery, "str", "cFormatXLS", _
"C:\PendingOrders_" & Format(Now(), "dd.mm.yyyy") & ".xls", True

DoCmd.OutputTo acOutputQuery, strQDF, acFormatXLS, "C:\PendingOrders_" & a & ".xls", False

CurrentDb.QueryDefs.Delete strQDF

Its big...But bottom line isit shows error 2487, the Object Type argument for action or method is blank or invalid.

[rpeare]

put code markers around your code to get it to be more readable like this (use the word CODE in square brackets to start your code /CODE in square brackets to end your code):

Private Sub btPending_Click()
Dim penddate As Date
Dim a, str As String
    Dim qdfTemp As DAO.QueryDef
    Dim strQDF As String

If (CStr(Format(Now(), "dddd")) <> "Tuesday" Or CStr(Format(Now(), "dddd")) <> "Thursday") Then
     If MsgBox("Today is not Tuesday/Thursday" & vbLf & "Do you Still want to continue", vbYesNo) = vbNo Then
         Exit Sub
     End If
End If

penddate = PlusWorkdays(Format("11/12/2013", "dd/mm/yyyy"), -10)
'To Do

str = " ...... " & _
"WHERE (((orderInvoice.dateRecieved)<#" & penddate & " # )  AND ((partnerBiWeekly.Description)=" & Chr(34) & "Daily" &  Chr(34) & ") AND ((orderList.deliveredDate)<> " & Chr(34)  & "Cancelled" & Chr(34) & ") AND ((orderList.deliveredDate)  Is Null))"

Debug.Print str

Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, str)
 qdfTemp.Close
        Set qdfTemp = Nothing

a = Format(Now(), "dd.mm.yyyy")

'DoCmd.TransferSpreadsheet , acExport, acTypespreadsheetTypeExcel9, strQDF, "C:\PendingOrders_" & a & ".xls", False
'DoCmd.OutputTo acQuery, "str", "cFormatXLS", "C:\PendingOrders_" & Format(Now(), "dd.mm.yyyy") & ".xls", True
      DoCmd.OutputTo acOutputQuery, strQDF, acFormatXLS, "C:\PendingOrders_" & a & ".xls", False
 
  CurrentDb.QueryDefs.Delete strQDF

I assume based on your post that this is erroring out on this line:

DoCmd.OutputTo acOutputQuery, strQDF, acFormatXLS, "C:\PendingOrders_" & a & ".xls", False


Where are you setting strQDF? looks like you're building a SQL string under the STR variable but I dont' see where strqdf is being defined.

If it's in a different part of your code and you debug.print str and actually paste that SQL code into a query window do you get results you expect or does the query itself bomb out?

EDIT:

Is there a reason you're trying to save this as a static query every time you run it? you can export to an excel file based on a valid SQL statement without saving it in your database in most instances.

[drunkenneo]

Here date is calulated on basis of working days and query is made for that, is there any other way to do it

[rpeare]

I do not understand your response

[drunkenneo]

From the table there is column daterecieved, and i have to see if the date recieved is above 10 working days then i need names of theclientsin an excel sheet.

Here i have calcolated back 10 working days from function plusworkingdaysfrom now, here after calculating the date i search the date is more than pendate on that basis i eprt the sheet

[drunkenneo]

I have solved the error and now the code is working superb, thanks for lot of combinations