Parameters in Report 1

[errolf]

Iam using a form to send Parameters to a Query used to generate a report with Dynamic Column headings. I can get the report to work to work until I use a second Query to get further data. The error message is "To few parameters expected 5" The following is The query SQL:-

PARAMETERS [Forms]![GsDataFrm]![BeginningDate] DateTime, [Forms]![GsDataFrm]![EndingDate] DateTime, SalesPerson Text ( 255 );
TRANSFORM Count(Salmantest.DATE) AS CountOfDATE
SELECT DebtorsTbl.NUMBER, DebtorsTbl.NAME AS Debtor, DebtorsTbl.SALES_LY, SALES_PERSON.NAME, YtdConsolQry.Current, YtdConsolQry.[Current-365], Count(Salmantest.DATE) AS TotCalls
FROM ((Salmantest INNER JOIN DebtorsTbl ON Salmantest.AC_NO = DebtorsTbl.NUMBER) INNER JOIN SALES_PERSON ON Salmantest.SMAN = SALES_PERSON.SMAN_NO) LEFT JOIN YtdConsolQry ON DebtorsTbl.NUMBER = YtdConsolQry.AC_NO
WHERE (((SALES_PERSON.NAME)=[SalesPerson]) AND ((Salmantest.DATE) Between [Forms]![GsDataFrm]![BeginningDate] And [Forms]![GsDataFrm]![EndingDate])) OR (((Salmantest.DATE) Between [Forms]![GsDataFrm]![BeginningDate] And [Forms]![GsDataFrm]![EndingDate]) AND (([SalesPerson]) Is Null))
GROUP BY DebtorsTbl.NUMBER, DebtorsTbl.NAME, DebtorsTbl.SALES_LY, SALES_PERSON.NAME, YtdConsolQry.Current, YtdConsolQry.[Current-365], DebtorsTbl.ADDR1
PIVOT Format([Date],'mmm-yy') In ('Jan-04','Feb-04','Mar-04','Apr-04','May-04','Jun-04','Jul-04','Aug-04','Sep-04','Oct-04');

This Sql is generated from a Cmd Button on a form and the query works fine.
My problem is the report as I don't know how to add the required parameters to the report.
The section relating to the parameters in the report Is :-

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

' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("GsDataFrm")) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "GsDataFrm in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!gsdatafrm
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("test")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!GsDataFrm!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!GsDataFrm!EndingDate") _
= frm!EndingDate

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
I assume that my problem is in the parameters section but I can't get the code in the parameter section correct.

Any guidance appreciated

Errol Featherstone

 

[KenReay]

Hi

There are three parameters StartDate, EndDate and Sales Person

You are only setting the value of two of them in code as far as I can see

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[errolf]

Thanks Ken,
The rest of the parameters relate to a secondary Query that gets its parameters from the same form [GsdataFrm].
My problem is how to reference the parameters in the secondary Query.

Errol Featherstone

 

[KenReay]

Same technique surely?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK