Search code examples
ms-access

I need to generate a report from a data in a searched date range

I have a database which I have been able to design a split form where am able to search by date and the results are displayed in the datasheet below.

My issue is, is there any code to help me store the data displayed in the data sheet into a table so I can easily link it to a designed report for printing?

Or better still, is it possible for my designed report to easily pick the records searched in the date range without passing it through a table?

There are my codes for the split form

Private Sub Command20_Click()
' Search button
Call Search
End Sub

Sub Search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Or IsNull(Me.OrderDateTo) Then
    MsgBox "Please enter the date range", vbInformation, "Date Range Required"
    Me.OrderDateFrom.SetFocus

Else
    strCriteria = "([DATE] >= #" & Me.OrderDateFrom & "# And [DATE] <= #" & Me.OrderDateTo & "#)"
    task = "select * from ALL_INCOME where (" & strCriteria & ") order by [DATE]"
    DoCmd.ApplyFilter task
    
End If
End Sub

I would like to create a button to print this report.

Any help with this will be greatly appreciated.

Thanks in advance.

Solution

  • You can directly filter report data by following command. Adjust report name, table field name to your case.

    DoCmd.OpenReport "rptAllInvoice", acViewPreview, , "[MyDate] BETWEEN #" & Me.OrderDateFrom & "# AND #" & Me.OrderDateTo & "#", acWindowNormal

    Here rptAllInvoice is report name and MyDate is date field to table.