Code Error

[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.

 

[pr2-eugin]

Should it not be qdfTemp that needs to be deleted?

I am a bit lost to be honest.. What is that you are trying to do?

 

[Drunkenneo]

here i am searching for the records whose daterecived is after 10 working days and exporting the result in Excel.

Me too had made this roughly and stucked in between.

 

[pr2-eugin]

So technically this?

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

    pendDate = PlusWorkdays(Format("11/12/2013", "dd/mm/yyyy"), -10)
    [COLOR=Green]'Should the above not be : 
    '[/COLOR][COLOR=Green]pendDate = PlusWorkdays(#12/11/2013#, -10)[/COLOR]
[COLOR=Green]    'pendDate = PlusWorkdays(Date(), -10)[/COLOR]
    
    str = "SELECT whateverYouWant FROM oneTable JOIN someOtherTable ON " & _
          "WHERE ((orderInvoice.dateRecieved < " & Format(pendDate, "\#mm\/dd\/yyyy\#") & ") AND " & _
          "(partnerBiWeekly.Description = 'Daily') AND (orderList.deliveredDate <> 'Cancelled') AND (orderList.deliveredDate Is Null))"

    [COLOR=Red][B]strQDF = "tmpQry"[/B][/COLOR]
    Debug.Print str

    Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, str)
    qdfTemp.Close
    Set qdfTemp = Nothing
    
    DoCmd.OutputTo acOutputQuery, strQDF, acFormatXLS, "C:\PendingOrders_" & Format(Date(), "dd.mm.yyyy") & ".xls", False

    CurrentDb.QueryDefs.Delete strQDF
End Sub

I am not sure what you have in your User Defined PlusWorkdays function, but check out the comments..

 

[Drunkenneo]

Thanks that was silly: still stucked on docmd line as says: Error 2498 : An expression you entered is the wrong data type for one of these arguments.

 

[pr2-eugin]

Can you open the Query (that has just been created) and manually export it? I cannot see anything wrong..

Specially if that is happening in the DoCmd line.

 

[JHB]

What is the value of "strQDF", I can't see it is getting any value?

 

[pr2-eugin]

I think I might as well have figured my error after reading JHB's post.. Cheers

Change the assignment as..

    strQDF = "tmpQry"

 

[Drunkenneo]

yes, thanks i have solved the mystry,

the above code was making a temp query from strqdf all i nedded to dao was to export the query, not the table