G
Guest
I have Access 2000. I am trying to create a passthrough query that will
reference a form for the parameters. There are two tables on a SQL server
linked with an inner join (DATAWSQL.dbo.INVOICE_HEADER and
DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS).
I am a beginner & have been reading the various postings on this, but
continue to get errors and such when using the ideas I find. Here is what I
have tried so far. FYI, when I tried to define db as a dao.database & qdf as
dao.querydef I got compilation errors. When I try it the following way I get
Error 3265 - Item not found in this collection.
Please help?
Thank you - Jennifer
Private Sub cmdRunQuery_Click()
On Error GoTo ProcError
Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)
qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"
ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub
reference a form for the parameters. There are two tables on a SQL server
linked with an inner join (DATAWSQL.dbo.INVOICE_HEADER and
DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS).
I am a beginner & have been reading the various postings on this, but
continue to get errors and such when using the ideas I find. Here is what I
have tried so far. FYI, when I tried to define db as a dao.database & qdf as
dao.querydef I got compilation errors. When I try it the following way I get
Error 3265 - Item not found in this collection.
Please help?
Thank you - Jennifer
Private Sub cmdRunQuery_Click()
On Error GoTo ProcError
Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)
qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"
ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub