Access 97 supply variable parameters to SQL Passthrough Query

[syoung4]

Please can anyone help me. I am trying to supply two variable input dates to an SQL Specific passthrough query from and Access 97 Database.

The table is on a remote server.
This is the query I have so far it works if the table is in my database but not when I use the linked table.

select ReceiptID,ReceiverMessageID,Status,OriginatingProcessID,SupplierReferenceNumber,StatusMessageDate,ExceptionText From tblInQueuee537Receipt where status = 'failure' and StatusMessageDate >= [Enter Date From] and <[Enter Date To]

I want to be able to input two dates from and to to run the query and would like it to prompt me for these.
Thank you.

 

[PHV]

I'm not aware a passthrough query admits parameters.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[syoung4]

I didnt think their was. I have been using this code on my other queries that uses one query that has place holders eg. 'myWkEnd1','myWkEnd2' the query uses a date supplied from a form and creates a temp table and another query with all the correct details filled in.


'KEY FUNCTION
'creates SQL from input on form + base query
'save this to 'temp' query
'copy base report to 'temp' report and change record source to 'temp query'

Function createTempQuery()
Dim DB As Database
Dim sQryName As String
Dim sSQL As String, sSQLorg As String
Dim bDates As Boolean
Dim I As Integer


sQryName = "qryPAYMENTCERTIFICATES"
bDates = Me.cmbWeekEnding

'grab current SQL
Set DB = CurrentDb()
sSQL = DB.QueryDefs(sQryName).SQL
sSQLorg = sSQL


'make modification to SQL - if arent necessary as criteria
If bDates Then
'i = InStr(1, sSQL, "weekending>='1900-01-01
'sSQL = strReplace(sSQL, "weekending>='1900-01-01'", "weekending>='" & Format(Me.cmbWeekStart.Value))
'sSQL = strReplace(sSQL, "myWkEnd1", Format(Me.cmbWeekEnding.Value, "yyyy-mm-dd"))
sSQL = strReplace(sSQL, "myWkEnd1", (Me.cmbWeekEnding.Value))
sSQL = strReplace(sSQL, "myWkEnd2", (Me.cmbWeekEnding.Value))
sSQL = strReplace(sSQL, "myWkEnd3", (Me.cmbWeekEnding.Value))
sSQL = strReplace(sSQL, "myWkEnd4", (Me.cmbWeekEnding.Value))
'sSQL = strReplace(sSQL, "myWkEnd5", Format(Me.cmbWeekEnding.Value, "yyyy-mm-dd"))
sSQL = strReplace(sSQL, "myOrganisationID1", (Me.cmbOrganisation.Value))
sSQL = strReplace(sSQL, "myOrganisationID2", (Me.cmbOrganisation.Value))
sSQL = strReplace(sSQL, "myOrganisationID3", (Me.cmbOrganisation.Value))
sSQL = strReplace(sSQL, "myOrganisationID4", (Me.cmbOrganisation.Value))
'sSQL = strReplace(sSQL, "myOrganisationID5", (Me.cmbOrganisation.Value))
End If




'save modified query as 'temp'
DB.QueryDefs("PaymentCertificate").SQL = sSQL


' Delete the table first before making a new table.
DB.Execute "DROP TABLE [PaymentCertificatetmp];"

'Now Create the table with new values
DB.Execute "SELECT PaymentCertificate.* INTO " _
& "[PaymentCertificatetmp] FROM PaymentCertificate;"

' Delete the ContractNametmp table first before making a new table.
DB.Execute "DROP TABLE [ContractNametmp];"

'Now Create the ContractNametmp table with new values
DB.Execute "SELECT PaymentCertificatetmp.ContractName INTO " _
& "[ContractNametmp] FROM PaymentCertificatetmp;"

Thank you for your help.
regards,

Sid.