Problem passing parameters to a Report using a parameter query (1 Viewer)

AlphaMu

New member
Local time
Today, 09:51
Joined
Jul 14, 2018
Messages
6
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

Wino Moderator
Staff member
Local time
Today, 09:51
Joined
Aug 30, 2003
Messages
36,118
I've never used the first method. The second would need to refer to the field name and use Between.
 

AlphaMu

New member
Local time
Today, 09:51
Joined
Jul 14, 2018
Messages
6
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

New member
Local time
Today, 09:51
Joined
Jul 14, 2018
Messages
6
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

Wino Moderator
Staff member
Local time
Today, 09:51
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 09:51
Joined
Jul 14, 2018
Messages
6
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

Wino Moderator
Staff member
Local time
Today, 09:51
Joined
Aug 30, 2003
Messages
36,118
The D1 and D2 must be coming from the query. The other needs to be a field returned by the query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:51
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 09:51
Joined
Jul 14, 2018
Messages
6
Thanks MajP you nailed it. I'm gonna sleep well tonight.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:51
Joined
Jan 23, 2006
Messages
15,364
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

You've got your good things, and you've got mine.
Local time
Today, 12:51
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom