View Single Post
  #4 (permalink)  
Old April 22nd, 2004, 07:42 PM
blinton25 blinton25 is offline
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

In reponse to:

"Hello blinton25

You received the following message from: benmay14 (blm14@columbia.edu)

At: http://p2p.wrox.com/

Did you ever get report using a pass through query with arguments
problem solved? I am trying to do this myself... The problem I am describing
is available here:

http://p2p.wrox.com/topic.asp?TOPIC_ID=5485

Any help you could provide me would be greatly appreciated. Thanks!
"

Hi,

Sorry about the delay in responding, here goes:

1. Make the record source of the report TArrivalsByRegion.

2. In report open, create a querydef, to which you will append your form parameters.

Private Sub Report_Open(Cancel As Integer)

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form


          ' Set database variable to current database.
     Set dbsReport = CurrentDb
     Set frm = Forms!DateRangePreviousCurrentYear
     ' Open QueryDef object.
     Set qdf = dbsReport.QueryDefs("TArrivalsByRegion")


 Dim rstReport As DAO.Recordset

qdf.SQL = "EXEC TArrivalsByRegion " & Forms!DateRangePreviousCurrentYear!PrevYear & "," & Forms!DateRangePreviousCurrentYear!CurrYear
qdf.ReturnsRecords = True


 ' Open Recordset object.

      Set rstReport = qdf.OpenRecordset()
rstReport.MoveFirst


3. Create a Passthough query which contains the following statement:

EXEC TArrivalsByRegion

4. Create a Stored Procedure with the following:


CREATE PROCEDURE TArrivalsByRegion

@PrevYear1 varchar(4),@CurrYear1 varchar(4)

AS

SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@Curr Year1)

Go


Interesting thing to note, after I run the code and check the Passthrough query it has:

EXEC TArrivalsByRegion 2001,2003

if I used the parameters 2001, 2003.

Looks like if I hardcoded the values but this isn't so because if I try different parameters, e.g 2001, 2002 the Passthough query is updated with these parameters.

Let me know if this helps.
Reply With Quote