Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 08:42 PM   #1
AlphaMu
Newly Registered User
 
Join Date: Jul 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
AlphaMu is on a distinguished road
Problem passing parameters to a Report using a parameter query

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

AlphaMu is offline   Reply With Quote
Old 07-13-2018, 10:03 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Problem passing parameters to a Report using a parameter query

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-14-2018, 09:46 AM   #3
AlphaMu
Newly Registered User
 
Join Date: Jul 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
AlphaMu is on a distinguished road
Re: Problem passing parameters to a Report using a parameter query

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 is offline   Reply With Quote
Old 07-14-2018, 09:57 AM   #4
AlphaMu
Newly Registered User
 
Join Date: Jul 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
AlphaMu is on a distinguished road
Re: Problem passing parameters to a Report using a parameter query

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
AlphaMu is offline   Reply With Quote
Old 07-14-2018, 09:59 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Problem passing parameters to a Report using a parameter query

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-14-2018, 10:23 AM   #6
AlphaMu
Newly Registered User
 
Join Date: Jul 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
AlphaMu is on a distinguished road
Re: Problem passing parameters to a Report using a parameter query

Hi PBaldy

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



Thanks for your help and suggestion
AlphaMu is offline   Reply With Quote
Old 07-14-2018, 10:58 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Problem passing parameters to a Report using a parameter query

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

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-14-2018, 11:12 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 197
Thanks: 2
Thanked 48 Times in 47 Posts
MajP will become famous soon enough
Re: Problem passing parameters to a Report using a parameter query

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#"
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
AlphaMu (07-14-2018)
Old 07-14-2018, 02:37 PM   #9
AlphaMu
Newly Registered User
 
Join Date: Jul 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
AlphaMu is on a distinguished road
Re: Problem passing parameters to a Report using a parameter query

Thanks MajP you nailed it. I'm gonna sleep well tonight.
AlphaMu is offline   Reply With Quote
Old 07-14-2018, 04:17 PM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,364
Thanks: 49
Thanked 1,844 Times in 1,794 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Problem passing parameters to a Report using a parameter query

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old Yesterday, 05:49 AM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 197
Thanks: 2
Thanked 48 Times in 47 Posts
MajP will become famous soon enough
Re: Problem passing parameters to a Report using a parameter query

Quote:
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.

MajP is online now   Reply With Quote
Reply

Tags
parameter report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Passing Query Parameters to a Report WillM Reports 4 08-06-2014 04:17 AM
Passing query parameters (in Tables) where the parameter varies David8 Theory and practice of database design 2 10-30-2010 02:35 AM
passing parameters to a query-based report J. Davis Forms 5 03-08-2010 01:19 AM
Passing Query Parameters to a report chris_jolly Reports 17 09-29-2007 08:26 AM
Problem passing parameter to report [DoCmd] Elfman General 13 09-21-2007 10:21 AM




All times are GMT -8. The time now is 04:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World