0

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.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Tete
  • 17
  • 4
  • You do not need to store filtered data to table. You can directly filter report data like your form. Better approach would be design a query and user dates from your form as query criteria. Then use that query as report record source. – Harun24hr Apr 26 '21 at 17:30
  • See this article [DoCmd.OpenReport filtering by date](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother-mso_2010/docmdopenreport-filtering-by-date/dc2aeeca-953a-4d51-bc5c-dc77ecd575d7) – Harun24hr Apr 26 '21 at 17:40
  • Does this answer your question? [MS Access - Docmd.OpenReport](https://stackoverflow.com/questions/50346797/ms-access-docmd-openreport) – June7 Apr 26 '21 at 17:42
  • Please a novice to all these. Can you please help me on how to do it so I can just copy and paste – Tete Apr 26 '21 at 17:44

1 Answers1

0

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.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • So if I understand you. I will still use my table All_INCOME as the source of the report. Then I create a button where I will put these codes there right? – Tete Apr 26 '21 at 19:24
  • I have tried your code and it worked perfectly. Thanks so much for your assistance – Tete Apr 26 '21 at 22:34
  • Then try to formatting your date condition as per your computer settings. You can try `"[MyDate] BETWEEN " & Format$(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & " AND " & Format$(Me.OrderDateTo, "\#mm\/dd\/yyyy\#")`. – Harun24hr Apr 27 '21 at 03:23
  • Thanks so much Sir. I really appreciate – Tete Apr 27 '21 at 08:06
  • @Tete If it helps then please consider accepting the answer (Tick Mark) it. – Harun24hr Apr 27 '21 at 08:07