MS Access Multiple Report Parameter

Question

I have 3 subreports based on their respective union query (Previous month, current month, and next month) and all have the same 2 parameters (month and year). When I place the 3 subreports onto an overall report, I have to type each parameter in 3 times. Is there a way to make it so I'd only have to type month and year in once and that is able to pass-through to the other parameters? Preferably no visual basic as the others who will be working with this have little knowledge of coding...

Cheers mates and thanks in advance.

I have them in the original queries (the ones the union queries pull from) set as just a standard parameter. — Dhill
Can you post an example? Likely you can just reference a form control instead of a parameter. That's the easiest thing usually (though sometimes not the best) but let's see your query. — Brad
Which query should I post? I have a staff form that allows for up to 5 project codes. The codes are then pulled into 5 different queries (which have the parameters) and then they are unioned into a current month query which has its SQL Adjusted twice to create a next month and a previous month. — Dhill

Brad Brad · Accepted Answer · 2016-06-28T20:17:03

You probably have a query like

Select * 
From SomeTable ST
Where ST.aColumn = YourParameter

Where YourParameter is what shows up in a popup dialog. Access asks for this because it is it is not the name of a column and can't resolve it to a control and you didn't provide a value (something you can do via VBA. A little more robust because it let's you specify parameters that are not bound to a particular form. Can also be called from outside Access)

You can reference a control instead though. So let's say the form which opens your report is called ReportOpener then you can have a textbox on your form called txtParameter (which you validate after your button click but before you open the report). Now your query can look like this

Select * 
From SomeTable ST
Where ST.aColumn = Forms!ReportOpener!txtParameter

This will only work if the form ReportOpener is open though, otherwise you'll get the same popup. Repeat this same logic for each parameter.

You can have a hidden text box which defaults to =Date() an duse that in your queries

Select * 
From SomeTable ST
Where ST.aDateColumn >= DateSerial(Year(Forms!ReportOpener!txtParameter), Month(Forms!ReportOpener!txtParameter) - 1, 1)
  and ST.aDateColumn < DateSerial(Year(Forms!ReportOpener!txtParameter), Month(Forms!ReportOpener!txtParameter) +2 1, 1)

So for example, today's date is 6/28/2016 which is the value of Date() which means

DateSerial(Year(Date), Month(Date) - 1, 1) = 5/1/2016

and

DateSerial(Year(Date), Month(Date) + 2, 1) = 8/1/2016

Do your date column would be bounded by

DateColumns >= 5/1/2016 and DateColumn < 8/1/2016

Which is last month, this month and next month.

Even though this parameter could be replaced entirely by Date it is still next to use the parameter because that way you can change what 3 month interval you are looking at.

MS Access Multiple Report Parameter

1 Answers