ms-access: report that displays records only within a certain date

Question

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>?

Answer 1

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

Answer 2

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:

1. Specify the date field like so in the query design:

   Format([YourDateField],"mmm. dd, yyyy")

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

Answer 3

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:

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.