vba ms-access：报告仅显示特定日期内的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2806413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ms-access: report that displays records only within a certain date
提问by l--''''''---------''''''''''''
i have a very simple report that is generated from just one table. one of the columns in the table is a date.
我有一个非常简单的报告，它仅从一张表中生成。表中的一列是日期。
i need to be able to allow the user of the report to enter a range of dates and display data only between those dates.
我需要能够允许报告的用户输入日期范围并仅显示这些日期之间的数据。
how do i do this>?
我该怎么做>？
回答by David-W-Fenton
I don't like hardwiring either parameters or form references in the recordsources of forms/reports, so I would amend @Remou's idea to instead set the RecordSource in the OnOpen event of the report. That is, first open the form, collect the values of the selected dates, and then plug those into the where clause of the report's RecordSource. Something like this (copied from a real report of mine):
我不喜欢在表单/报告的记录源中硬连接参数或表单引用，所以我会修改@Remou 的想法，改为在报告的 OnOpen 事件中设置 RecordSource。也就是说，首先打开表单，收集所选日期的值，然后将这些值插入到报表的 RecordSource 的 where 子句中。像这样（从我的真实报告中复制）：
Dim strRecordSource As String
DoCmd.OpenForm "dlgDateRange", , , , , acDialog, "ThisYear"
If IsLoaded("dlgDateRange") Then
With Forms!dlgDateRange
If .Tag = "Cancel" Then
Cancel = True
Else
Me.Filter = "[InvoiceDate] Between #" & !txtStart & "# AND #" & !txtEnd & "#"
Me.FilterOn = True
Me!lblDateRange.Caption = StrConv(Trim(("from " + varZLStoNull(Format(!txtStart, "mm/dd/yyyy"))) & (" to " + varZLStoNull(Format(!txtEnd, "mm/dd/yyyy")))), vbProperCase)
End If
End With
DoCmd.Close acForm, "dlgDateRange"
End If
Some comments:
一些评论：
- The dialog form called here is much more complex than what you need, as it has a bunch of predefined date ranges, set based on the dialog form's OpenArgs parameter. The form looks like this:
- 此处调用的对话框表单比您需要的要复杂得多，因为它有一堆预定义的日期范围，根据对话框表单的 OpenArgs 参数设置。表格如下所示：
(source: dfenton.com)
（来源：dfenton.com）
I use Stephan Lebans date picker codefor allowing the user to pick a date from a calendar control.
我使用Stephan Lebans 日期选择器代码来允许用户从日历控件中选择一个日期。
The code for setting the date ranges is this, and all I have to do is pass it one of the cases of this CASE SELECT:
设置日期范围的代码是这样的，我所要做的就是将这个 CASE SELECT 的情况之一传递给它：
Public Sub SetDates(strType As String, ctlStart As Control, ctlEnd As Control)
Dim dteStart As Date
Dim dteEnd As Date
Dim ctl As Control
Select Case strType
Case "EndOnly" ' OK
dteStart = #1/1/1980#
ctlStart.Enabled = False
dteEnd = Date
Case "Trace" ' OK
dteStart = DateAdd("d", -7, Date)
dteEnd = DateAdd("d", 7, Date)
Case "LastWeek" ' OK
dteStart = Date - Weekday(Date, vbMonday) - 6
dteEnd = dteStart + 6
Case "ThisWeek" ' OK
dteStart = Date - Weekday(Date, vbMonday) + 1
dteEnd = dteStart + 6
Case "LastMonth" ' OK
dteStart = month(DateAdd("m", -1, Date)) & "/01/" & year(DateAdd("m", -1, Date))
dteEnd = DateAdd("m", 1, dteStart) - 1
Case "ThisMonth" ' OK
dteStart = month(Date) & "/01/" & year(Date)
dteEnd = DateAdd("m", 1, dteStart) - 1
Case "LastQuarter" ' OK
dteStart = DateSerial(year(DateAdd("q", -1, Date)), (3 * Format(DateAdd("q", -1, Date), "q")) - 2, 1)
dteEnd = DateAdd("q", 1, dteStart) - 1
Case "ThisQuarter" ' OK
dteStart = DateSerial(year(Date), (3 * Format(Date, "q")) - 2, 1)
dteEnd = DateAdd("q", 1, dteStart) - 1
Case "LastYear" ' OK
dteStart = "01/01/" & year(Date) - 1
dteEnd = "12/31/" & year(Date) - 1
Case "ThisYear" ' OK
dteStart = "01/01/" & year(Date)
dteEnd = "12/31/" & year(Date)
Case "LastFY" ' OK
dteStart = "09/01/" & year(DateAdd("m", 4, Date)) - 2
dteEnd = DateAdd("yyyy", 1, dteStart) - 1
Case "ThisFY" ' OK
dteStart = "09/01/" & year(DateAdd("m", 4, Date)) - 1
dteEnd = DateAdd("yyyy", 1, dteStart) - 1
Case "Last3Years" ' OK
dteStart = "01/01/" & year(Date) - 2
dteEnd = Date
Case "BeforeLast3Years" ' OK
dteEnd = DateValue("01/01/" & year(Date) - 2) - 1
Case Else
dteStart = Date
dteEnd = Date
End Select
If ctlStart.Enabled Then
If dteStart = 0 Then
ctlStart = Null
Else
ctlStart = Format(dteStart, "mm/dd/yyyy")
End If
End If
If ctlEnd.Enabled Then
If dteEnd = 0 Then
ctlEnd = Null
Else
ctlEnd = Format(dteEnd, "mm/dd/yyyy")
End If
End If
For Each ctl In ctlStart.Parent!optPresetDates.Controls
If ctl.ControlType <> acLabel Then
If Replace(ctl.Controls(0).Caption, " ", vbNullString) = strType Then
ctlStart.Parent!optPresetDates = ctl.OptionValue
Exit For
End If
End If
Next ctl
Set ctl = Nothing
End Sub
That's way more information than you need, really, but the point I'm trying to make is that you should consider tying your report's recordsource to parameters or a dialog form.
这确实比您需要的信息多得多，但我想说明的是，您应该考虑将报表的记录源与参数或对话框表单联系起来。
回答by Fionnuala
The best solution is probably to create a small form that allows the user to enter dates. The query that the report is based on can then refer to the form:
最好的解决方案可能是创建一个允许用户输入日期的小表单。报告所基于的查询然后可以参考以下表格：
SELECT f1,f2,f3 FROM Table
WHERE SomeDate
BETWEEN Forms!DateSelect!StartDate AND Forms!DateSelect!EndDate
回答by Zoli
What I would do is create a query that selects all of the rows in the table and for the date field I'd set up a couple of parameters. Try the following:
我要做的是创建一个查询来选择表中的所有行，并为日期字段设置几个参数。请尝试以下操作：
Specify the date field like so in the query design:
Format([YourDateField],"mmm. dd, yyyy")
And for the criteria write:
Between Format([From],"mmm. yy, dddd") And Format([To],"mmm. dd. yyyy")
在查询设计中像这样指定日期字段：
Format([YourDateField],"mmm.dd, yyyy")
对于标准写：
在 Format([From],"mmm.yy, dddd") 和 Format([To],"mmm.dd.yyyy") 之间
When you run the query, two input boxes should come up asking for the Fromand Todates in the specified format.
运行查询时，应出现两个输入框，要求输入指定格式的日期From和To日期。
