Results 1 to 15 of 15
  1. #1
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18

    Report filtering by Form

    Hi all, I need some help, please!
    I have VBA in Form filter to open report by Date range. It works perfect, but I wanna to set few more criteria comboboxes..
    This is VBA:


    Private Sub cmd_Filter_Click()
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    strReport = "rpt_ reference"
    strDateField = "[Date]"
    lngView = acViewPreview

    If IsDate(Me.txt.Datefrom) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txt.Datefrom, strcJetDate) & ")"
    End If
    If IsDate(Me.Dateto) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.Dateto + 1, strcJetDate) & ")"
    End If
    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    DoCmd.OpenReport strReport, lngView, , strWhere
    Exit_Handler:
    Exit Sub

    Err_Handler:
    If Err.Number <> 2501 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
    End Sub
    Thise code is working perfect for the date from – to, but I wanna to set criteria for cbo_Inspektor, where there is field “Inspektor” in my query.
    What it should be the code??? I want also to have message if the cbo_...... is empty…..” please enter a value”

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    Quote Originally Posted by June7 View Post
    Тhanks for the reply! I looked at it, but I want to add to my code criteria for cbo_Inspektor.
    Is It possible and how to write it?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're going to have to give us a little more detail as to where cbo_Inspektor is located, its properties/data(rowsource).
    Perhaps you could post a copy of your database with instructions to highlight the issue/opportunity.
    It always helps to show readers a mock up/picture of your tables and relationships and your desired output.

    Good luck with your project.

  5. #5
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    Quote Originally Posted by orange View Post
    You're going to have to give us a little more detail as to where cbo_Inspektor is located, its properties/data(rowsource).
    Perhaps you could post a copy of your database with instructions to highlight the issue/opportunity.
    It always helps to show readers a mock up/picture of your tables and relationships and your desired output.

    Good luck with your project.
    I apologize for my inaccuracies. In my Filter for I have 2 text boxes (txtDatefrom and Dateti) and a combobox (cbo_Inspektor).
    The cbo_Inspektor have rowsource from field Inspector in qry which is my report

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Modify code from the referenced link to use your controls. Exactly what do you not understand about the example?

    Please post lengthy code within CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    Quote Originally Posted by June7 View Post
    Modify code from the referenced link to use your controls. Exactly what do you not understand about the example?

    Please post lengthy code within CODE tags to retain indentation and readability.
    Here is my DB....
    Please, help me....
    Attached Files Attached Files

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In order to get more focused responses, we need to know --even in overview --what is the subject matter and the business you are trying to support with this database??

  9. #9
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    Quote Originally Posted by orange View Post
    In order to get more focused responses, we need to know --even in overview --what is the subject matter and the business you are trying to support with this database??
    In order to get more focused responses, you need to know --even in overview -- the subject matter and the business I am trying to support with this database is --- А small company with control functions!
    Now, after you've got the valuable information about using the database, can you help with specific solutions to the problem or you just decided to write spam??????

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you think my response was spam that's unfortunate. I have looked at your database and post and do not understand What you are trying to accomplish.
    A simple statement like:
    We are a small Company who must report Sales of Products to which Customers on a Weekly basis.

    That would give readers a hint that you are a business
    -with Customers;
    -you have Products
    -you sell some qty of Product ( to Customers on specific Dates)
    -you Report weekly totals. (SalesTotals, possibly Inventory???)

    You will find that providing context to your post will help you and readers.
    Did you want readers to guess your requirements?

    Good luck with your project.

    Update: Try this insert into your existing code.


    If I have understood the requirement to add Inspector to the criteria then this code should
    add the Inspector from the cbo_Inspector to the strwhere


    Code:
    If IsDate(Me.txtTo) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtTo + 1, strcJetDate) & ")"
        End If
        '====
        If Not IsNull(Me.cbo_Inspector) Then
         strWhere = strWhere & " AND Inspector ='" & Me.cbo_Inspector & "'"
        End If
        '====
            
        'Close the report if already open: otherwise it won't filter properly.
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
    ?strwhere
    ([Data] >= #11/13/2018#) AND ([Data] < #12/01/2018#) AND Inspector ='Hristov'


    I hope that's what you were looking for.

  11. #11
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    Quote Originally Posted by orange View Post
    If you think my response was spam that's unfortunate. I have looked at your database and post and do not understand What you are trying to accomplish.
    A simple statement like:
    We are a small Company who must report Sales of Products to which Customers on a Weekly basis.

    That would give readers a hint that you are a business
    -with Customers;
    -you have Products
    -you sell some qty of Product ( to Customers on specific Dates)
    -you Report weekly totals. (SalesTotals, possibly Inventory???)

    You will find that providing context to your post will help you and readers.
    Did you want readers to guess your requirements?

    Good luck with your project.

    Update: Try this insert into your existing code.


    If I have understood the requirement to add Inspector to the criteria then this code should
    add the Inspector from the cbo_Inspector to the strwhere


    Code:
    If IsDate(Me.txtTo) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtTo + 1, strcJetDate) & ")"
        End If
        '====
        If Not IsNull(Me.cbo_Inspector) Then
         strWhere = strWhere & " AND Inspector ='" & Me.cbo_Inspector & "'"
        End If
        '====
            
        'Close the report if already open: otherwise it won't filter properly.
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
    ?strwhere
    ([Data] >= #11/13/2018#) AND ([Data] < #12/01/2018#) AND Inspector ='Hristov'


    I hope that's what you were looking for.
    When I try to add this:
    If Not IsNull(Me.cbo_Inspector) Then
    strWhere = strWhere & " AND Inspector ='" & Me.cbo_Inspector & "'"
    End If

    Always shows me this error:

    Attached Thumbnails Attached Thumbnails VBA_1.jpg   VBA_2.jpg  

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Tihmir
    When an experienced forum member asks you for additional information, that is done in order to clarify what is being asked.
    By providing additional useful information, it is more likely that someone can assist you properly.

    However by responding as you did, you will have put off many of the people who might have helped you.
    I would suggest you reconsider your response
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure of the issue at your end.

    I added this to the version of the db you attached in post #7
    Code:
     '==
        If Not IsNull(Me.cbo_Inspector) Then
            strWhere = strWhere & " AND Inspector ='" & Me.cbo_Inspector & "'"
            Debug.Print strWhere & "[ tested at " & Now & "]"
        End If
      '==
    This is output of debug.print
    ([Data] >= #11/05/2018#) AND ([Data] < #12/06/2018#) AND Inspector ='Filipova'[ tested at 16-Dec-18 12:01:19 PM]

    This is the filter/parm
    Click image for larger version. 

Name:	FilterUsed.PNG 
Views:	18 
Size:	9.2 KB 
ID:	36603

    This is the report
    Click image for larger version. 

Name:	TheReport.PNG 
Views:	17 
Size:	7.6 KB 
ID:	36604


    No errors occurred, but I do get a warning that the report is too wide or similar and some pages may be blank.

  14. #14
    tihmir is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    18
    ridders52
    I appreciate your advice!
    Thanks to everyone!
    The post is solved

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    And what was the solution??
    Someone else may have similar issue.
    That's the point of the forum --we share solutions.

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

Similar Threads

  1. Lookup in Form and Report filtering
    By agure in forum Access
    Replies: 15
    Last Post: 02-16-2014, 08:32 PM
  2. Replies: 4
    Last Post: 06-12-2012, 11:49 AM
  3. Generating Report from Form filtering
    By oica72 in forum Reports
    Replies: 5
    Last Post: 12-17-2011, 05:35 PM
  4. Replies: 0
    Last Post: 03-23-2011, 11:29 PM
  5. Replies: 0
    Last Post: 02-02-2011, 11:08 AM

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