MS Access多个报告参数 [英] MS Access Multiple Report Parameter

问题描述

我有3个基于各自的联合查询(上个月，本月和下个月)的子报表，并且都具有相同的2个参数(月和年).当我将3个子报表放到总体报表上时，我必须在3次中键入每个参数.有没有办法做到这一点，所以我只需要一次输入月份和年份，就可以传递到其他参数了?最好没有视觉基础，因为将要从事此工作的其他人几乎没有编码方面的知识...

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.

推荐答案

您可能有类似

Select * 
From SomeTable ST
Where ST.aColumn = YourParameter

YourParameter出现在弹出对话框中. Access之所以要求这样做，是因为它不是列的名称，并且不能将其解析为控件，并且您没有提供值(可以通过VBA来完成.这更加健壮，因为它允许您指定参数不受特定形式的限制.也可以从Access外部调用)

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)

不过，您可以引用一个控件.因此，假设打开报告的表单称为ReportOpener，那么您可以在表单上有一个名为txtParameter的文本框(您可以在单击按钮之后但在打开报告之前对其进行验证).现在您的查询看起来像这样

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

这仅在打开ReportOpener表单时有效，否则您将获得相同的弹出窗口.对每个参数重复相同的逻辑.

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.

您可以有一个隐藏的文本框，默认情况下为=Date()，这是您查询中的变色符号

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)

例如，今天的日期是6/28/2016，这是Date()的值，表示

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 

和

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

您的日期列是否会受到

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

上个月，本月和下个月.

Which is last month, this month and next month.

即使此参数可以完全替换为Date，它仍然是下一个使用该参数的方法，因为那样可以更改您要查看的3个月间隔.

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多个报告参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！