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.