Problem passing parameters to a Report using a parameter query

[AlphaMu]

Hi i have a big problem using a Query Parameter to open and populate a report.

This is the Parameter Query:

PARAMETERS D1 DateTime, D2 DateTime;
SELECT tblResidents.NomResident, tblResidents.PrenomResident, tblPaiements.Paiement
FROM tblResidents INNER JOIN tblPaiements ON tblResidents.NoResident = tblPaiements.NoResident
WHERE (((tblPaiements.DateDuPaiement) Between D1 And D2))
ORDER BY tblResidents.NomResident, tblPaiements.DateDuPaiement;


(It's in french but to give you the idea is that i want all the payments of the tenants from the 1 day of the month until the last day of the month. It's D1 and D2. If a run the query in QBE it's working.


The report is simple i set the recordsource to the name and there is only the tenantLastname, thenantfirstname and payments sort by TenantsLastName.


I Have this form from which I send the parameters to the report, I check the box which is the month i want my report.

I tried first with the DoCmd.SetParameter:



DoCmd.SetParameter "D1", #1/1/2018#
DoCmd.SetParameter "D2", #1/31/2018#

DoCmd.OpenReport "Etat", acViewLayout

This generate a empty report.


I also tried with the where clause of the doCmd.OpenReport


param1 = #1/1/2018#
param2 = #1/31/2018#

DoCmd.OpenReport "Etat", acViewLayout, , "D1 = " & param1 & " And D2 = " & param2

This also generate a empty report. It should print 7 records in january


Thanks

 

[pbaldy]

I've never used the first method. The second would need to refer to the field name and use Between.

 

[AlphaMu]

I try this but it's opening an empy Report


Dim chaine as string

param1 = #1/1/2018#
param2 = #1/31/2018#


DoCmd.OpenReport "Etat", acViewReport, , , , "D1=" & param1 & "|" & "D2=" & param2


chaine = Reports!Etat.OpenArgs

Print.Debug chaine

in the immediate window the result is

D1=2018-01-01|D2=2018-01-31



Thanks

 

[AlphaMu]

Sorry the code from the preceding code doesnt work at all. It doesnt show an empty report but keep asking for the parameter D1 and D2


Thanks

 

[pbaldy]

So you didn't try what I suggested?

DoCmd.OpenReport "Etat", acViewReport, , "DateDuPaiement Between #" & param1 & "# AND #" & param2 & "#"

Which assumes the criteria was taken out of the query.

 

[AlphaMu]

Hi PBaldy

I try what you said but it keep me asking for parameter D1, D2 and ... DateDuPaiment.



Thanks for your help and suggestion

 

[pbaldy]

The D1 and D2 must be coming from the query. The other needs to be a field returned by the query.

 

[MajP]

For some reason you have to pass the date as a string. This is not clear in the help file which clearly says a variant resolving to the expression. But the following worked for me.

DoCmd.SetParameter "D1", "#1/1/2018#"
DoCmd.SetParameter "D2", "#1/31/2018#"

 

[AlphaMu]

Thanks MajP you nailed it. I'm gonna sleep well tonight.

 

[jdraw]

I realize this is marked SOLVED, but here is a common approach to create Dynamic Reports.
Here is a link to a Martin Green tutorial on using Forms with parameters to create appropriate Record sources (and filters) and Openargs to achieve Dynamic Reports.

 

[MajP]

on using Forms with parameters to create appropriate Record sources (and filters) and Openargs to achieve Dynamic Reports.

Although helpful, that post does not address using parameters. It addresses passing a string filter and orderby. You do not see a lot of setting parameters by code in Access, but sometimes this technique can be easier.