Results 1 to 12 of 12
  1. #1
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272

    How to open a report in a date range whilst applying a combo list

    I have a database which am able to search data in a date range together with a combo list to get me the desired results displayed in a datasheet below it.


    Also, if data is searched without selecting any combo list item, data displays all records for the specified date range.

    Here are the codes for your consideration.


    Option Compare Database
    Option Explicit

    Private Sub cmdApplyFilter_Click()
    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 = "[MyDate] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
    " And [MyDate] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
    " AND [IncomeType] LIKE '" & IIf(IsNull(Me.cboIncomeType), "*", Me.cboIncomeType) & "'"
    'task = "select * from ALL_INCOME where (" & strCriteria & ") order by [DATE]"
    DoCmd.ApplyFilter , strCriteria

    End If
    End Sub




    I will like to be able to create a button on that same form so I cn print preview the data meeting the above criteria.


    I initially had the code which filtered the report in the date range but without any combo list selection. Here is the code


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


    Would be glad if anyone could help me modify this code to meet this new criteria am using

    Thank you

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Why not just use strCriteria as the criteria in the OpenReport?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Please a novice with vba codes.
    Would be glad if you could prepare the code for me to copy

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Code:
    DoCmd.OpenReport "rptAllInvoice", acViewPreview, ,strCriteria,  acWindowNormal
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Alrite. Let me try it out and get back to you please

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To that I would add 2 things.
    1) another approach is to create conditional where clauses. Since the dates are mandatory here, the only option is the combo, so one where clause uses the dates only IF the combo is null. If not, the combo portion just gets appended. That approach may come in handy when you have several optional criteria. It's easier than many IIF's IMO.

    2) strCriteria is a variant because it is not explicitly declared. It's a common mistake to think that "As String" applies to everything on one line. It does not. It's not going to cause an issue this time.
    Last edited by Micron; 04-29-2021 at 06:54 AM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Can you use my codes to restructure it for me to try?@ Micron

  8. #8
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    I tried this and it couldn’t work for the print preview report.
    Instead, all transactions for that particular date pops up
    I mean the strcriteria

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I left in the "task" stuff even though you don't seem to be using it. I also took the liberty of condensing by using the click event. All of this code is behind/on the form, yes? Then there's no need to use the click event to just call this procedure on the same form. If you'd rather have it your way, you should be able to fix it. Not sure why you have refresh either but I left it in. Hopefully I didn't mess up your criteria lines by moving them around. Suggest you copy the form and apply this to the copy as my sig says.

    Code:
    Private Sub cmdApplyFilter_Click()
    Dim strCriteria As String, 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
    Exit Sub
    End If
    
    strCriteria = "[MyDate] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
    " And [MyDate] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#")
    
    If Not IsNull(Me.cboIncomeType) Then
      strCriteria = strCriteria & " AND [IncomeType] = '" & Me.cboIncomeType & "'"
    End If
    
    'task = "select * from ALL_INCOME where (" & strCriteria & ") order by [DATE]"
    DoCmd.OpenReport "rptAllInvoice", acViewPreview, ,strCriteria,  acWindowNormal
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    So mean I should copy all these to my print preview button?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have no idea what your print preview button is but I'll say yes. It's for the same button as the one you posted your code for, which is called cmdApplyFilter. Or create a new button to play with and copy/paste it to there and see what happens.

    Just don't use provided code to make major mods to your one and only copy of a form unless you know what you're doing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Have tried it and it worked
    I copied the else condition followed by the open report condition and it gave me the desired results. Thanks so much for your time and efforts

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-06-2015, 05:06 AM
  2. Replies: 21
    Last Post: 04-29-2015, 11:57 PM
  3. date range instead list of dates
    By wnicole in forum Reports
    Replies: 2
    Last Post: 11-28-2013, 03:43 AM
  4. Open Report From List Using Combo Box
    By burrina in forum Forms
    Replies: 5
    Last Post: 11-20-2012, 10:20 AM
  5. Filter Report by Optional Date Range and Combo Box
    By ARickert in forum SQL Server
    Replies: 3
    Last Post: 10-17-2012, 10:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums