ms-access: report that displays records only within a certain date
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>?
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):
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:
(source: dfenton.com)
I use Stephan Lebans date picker code for allowing the user to pick a date from a calendar control.
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:
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.
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
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")
When you run the query, two input boxes should come up asking for the From
and To
dates in the specified format.
精彩评论