Ms-access: report that displays entries only for a specific date

Question

I have a very simple report that is generated from only one table. one of the columns in the table is the date.

I need to allow the report user to enter a range of dates and display data only between these dates.

how to do it>?

Answer 1

The best solution is probably to create a small form that allows the user to enter dates. The query on which the report is based can then refer to the form:

SELECT f1,f2,f3 FROM Table WHERE SomeDate BETWEEN Forms!DateSelect!StartDate AND Forms!DateSelect!EndDate 

Answer 2

I don’t like the tight binding of parameters or form links in the sources of form / report records, so I would modify the idea of ​​@Remou to set RecordSource in the OnOpen event of the report instead. That is, first open the form, collect the values ​​of the selected dates, and then paste them into the where clause of the RecordSource report. Something like this (copied from my real report):

  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 invoked here is much more complicated than you need, because it has a set of predefined date ranges set based on the OpenArgs parameter of the dialog form. The form is as follows:

_{(source: dfenton.com )}

I use Stephan Lebans date picker code to let the user select a date from a calendar control.

Here is the code to set the date ranges, and all I need to do is pass it in one of the cases of this CHOICE OF CASE:

  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 

Thus, there is actually more information than you need, but I want to emphasize that you should think about linking the source of the records of your report with the parameters or the dialog form.

Answer 3

What I would do is create a query that selects all the rows in the table, and for the date field I would set a couple of parameters. Try the following:

• Specify a date field similar to this in the query design:

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

• And for the criteria write:

  Between the format ([From], "mmm. Yy, dddd") and Format ([To], "mmm. Dd. Yyyy")

When you run the query, two input fields should appear asking for the From and To dates in the specified format.