Pass Thru Query

[RomeERome]

Hello Everyone,

I have created a SQL stored procedure to pull data from various SQL tables. I have also created a pass thru query to be able to not pull all of the data, but just records based on a date range.

The pass thru query's syntax that works looks as follows:

exec [cp_ProdRptPMSbyUser-1] '01/01/2012', '01/31/2012'

This syntax works fine, but I am trying to get this pass thru to recognize the values from two textboxes that contain date values on a form. I've tried the following as my pass thru syntax, but these don't work.

1. exec [cp_ProdRptPMSbyUser-1] ' & Forms!frmProdRpt!StrtDte & ', ' & Forms!frmProdRpt!EndDte & '

2. exec [cp_ProdRptPMSbyUser-1] Forms!frmProdRpt!StrtDte, Forms!frmProdRpt!EndDte

3. exec [cp_ProdRptPMSbyUser-1] "' & Forms!frmProdRpt!StrtDte & '", "' & Forms!frmProdRpt!EndDte & '"

4. exec [cp_ProdRptPMSbyUser-1] '" & Forms!frmProdRpt!StrtDte & "', '" & Forms!frmProdRpt!EndDte & "'

Can anyone assist me with this syntax?

Your assistance is greatly appreciated.

 

[dhookom]

You will need to change the SQL property of the saved pass-through query. Check faq701-7433.

Duane
Hook'D on Access
MS Access MVP

 

[RomeERome]

Hello Duane,

Thanks for responding. I was wondering can this be done without using VBA. I'm trying to make this as simple as possible. I'm pretty decent in VBA, but I just wanted to figure out if this was possible from the SQL view of query design.

 

[RomeERome]

Duane,

Sorry, I was trying to send you a picture of the SQL design view dialog, but you already know what that looks like. Sorry for any confusion.

 

[dhookom]

The vba is only a couple lines of significant code. It doesn't get much simpler in the VBA world.

The only other method I can think of is to create a table on the server to store the two date values. You can then modify the dates in Access and use the field values in your stored procedure.

Duane
Hook'D on Access
MS Access MVP