Help with Coding a Pass Through Query that Accepts Inputs 1

[Sydney1]

Good morning,

I have a stored procedure in Sql Server 2000 that accepts 2 input parameters. It is linked to an Access 97 DB as a pass through query, which is named qryCompanies. I am attempting to call this query from a button on a form where the user will input the parameters in 2 text boxes, which will be called forms!frmReportMenu!txtBeginDate and frmReportMenu!txtEndDateDate, and then click the button. After the user clicks this button, the form will be filtered by the pass through filter.

I have attempted to use QueryDef and parameters in VBA, but I haven't had any success. Any help or ideas with this issue would be greatly appreciated.

Thanks in advance for your help.

Sydney

 

[PWise]

try this

dim mydb as databasee
Set mydb=currentdb
mydb.QueryDefs("qryCompanies").sql = "SPname" &  frmReportMenu!txtBeginDate & "," & frmReportMenu!txtEndDateDate
docmd.openquery "qryCompanies"

Not tested

 

[Sydney1]

Thanks for your help.

I have the following code

Dim mydb As DAO.Database
Set mydb = CurrentDb
mydb.QueryDefs("qryCompanies").SQL = "sp_qryForeclosureReferralMain" & Forms!frmReportMenu1!txtBeginDate & "," & Forms!frmReportMenu1!txtEndDate
DoCmd.OpenQuery "qryCompanies"

But am getting a runtime error of 3146 " ODBC call failed at "DoCmd.OpenQuery "qryCompanies""

The 2 input parameters are date fields. The pass through query contains the 2 dates that I passed to it. It states

sp_qryForeclosureReferralMain12/31/2006,12/31/2007

When I run the pass through query on its own, I get the same error, due to the dates that are now contained in it.


I'm thinking that I may have to format the 2 date fields, but I'm not sure.
Again, thanks for your help. I really appreciate it.

Sydney

 

[PWise]

change this line
mydb.QueryDefs("qryCompanies").sql = "SPname" & frmReportMenu!txtBeginDate & "," & frmReportMenu!txtEndDateDate

to

mydb.QueryDefs("qryCompanies").sql = "sp_qryForeclosureReferralMain " &  frmReportMenu!txtBeginDate & "," & frmReportMenu!txtEndDateDate

note Space after Spname

 

[Sydney1]

Pwise,

Thanks for the quick response.

I upated the line of code to include the space after the name of the stored procedure. And now have the following:


Dim mydb As DAO.Database
Set mydb = CurrentDb
mydb.QueryDefs("qryCompanies").SQL = "sp_qryForeclosureReferralMain " & Forms!frmReportMenu1!txtBeginDate & "," & Forms!frmReportMenu1!txtEndDate
DoCmd.OpenQuery "qryCompanies"

But am still receiving the same runtime error as before.

When I attempt to run the query I get the following error. Incorrect syntax "/" The query looks like

sp_qryForeclosureReferralMain 12/31/2006,12/31/2007

Thanks to your help, I believe we're very close.

 

[PWise]

try

mydb.QueryDefs("qryCompanies").SQL = "sp_qryForeclosureReferralMain '" & Forms!frmReportMenu1!txtBeginDate & "','" & Forms!frmReportMenu1!txtEndDate & "'"

 

[RoyVidar]

I think format the date. I think SQL server will accept for instance yyyy-mm-dd or yyyymmdd format, and, not that I fire SPs like this, but shouldn't dates be delimited with single quotes for SQL server?

[tt]...ferralMain '" & Format$(Forms!frmReportMenu1!txtBeginDate, "yyyy-mm-dd") & "', '" ...[/tt]

Roy-Vidar

 

[Sydney1]

Pwise,

Your updated line of code worked! As RoyVidar (and thank you too for your help) stated: It had to be delimited with Single quotes for SQL Server.

Thanks so much your help. You've made my day.

Sydney