1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Form Filtered Result

Discussion in 'Business Applications' started by Heather112, Jan 3, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Hi Everyone,

    I have a Access Form set up so users can enter data that links back to an Access Table. The Table will be split so users with access to the Access Form will not have access to the Table. The users entering the data into the form aren't what I would call technically advanced which is why I don't want them to have access to the Table... I'd like to protect the integrity of the data in that way.

    However, I do want the users to be able to access certain stats about what they have entered into the Form in a controlled way. I would like to build a macro or separate Form that will:
    1) Prompt the user to enter their name or, ideally, allow them to choose from a list of names. This would link back to a field in the Form that contains a value set list of names including a "" option for the user to enter an alternative not included on the list.
    2) Next I would like to filter by a date range. I have been successful in doing this part. I created a new Form not bound by any query or table and generates a Report. The Form has two text boxes named txtStartDate and txtEndDate with a button named cmdPreview that On Click's property has this code (thank you http://allenbrowne.com/casu-08.html):
    Code:
    Private Sub cmdPreview_Click()
    'On Error GoTo Err_Handler     
       
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Const strcJetDate = "\#mm\/dd\/yyyy\#"  
        
        
        strReport = "rptSales"      
        strDateField = "[SaleDate]" 
        lngView = acViewPreview     
        
       
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        End If
        
    
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        Debug.Print strWhere      
        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
    
    I don't have to have the results open in a Report if there is a better way.

    3) I have the Report set to show me the line items from each invoice entered by each person. If it is possible, I would like instead to have only a sum of those line items (Table field is called [ITEMS ENTERED]) for that period selected above as well as doing a SUMIF type formula to give me a sum unique Invoice Numbers (labelled [INVOICE NUMBER] on the Table) for that person for that date range.

    Note: It is not strictly necessary that the users be able to filter by name if the Report can be set in a way that only shows the totals as per bullet 3 above. It does have to break it down by each user however. Also, the 'SUMIF' type calculation in bullet 3 is not strictly necessary either, more of a wish item than a must have.

    Any help anyone could provide would be much appreciated. I am not able to provide the worksheet for reference but I can certainly provide the names of the columns for reference if I have not done so accurately already.

    Thank you so very much!

    Heather
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What you want to do with summarising by Invoice is what Queries do best, it can then be displayed in a Report or on a Form.
    You need to select Totals, set Invoice Number to "Group by" and the value to be summed to "Sum".
    Do you have a table with the User Names(you should), if so you can just create a Combo to select the User.
     
  3. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Thank you for replying.

    I created a query and used the Invoice Number as the Group By, and set the Item line to Sum. However this doesn't allow me to sort by the individual user or filter by dates. These features I really need.

    Thank you again for your help.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can do the "Filtering" in a previous query that is used by your new query to get the values that you want.
     
  5. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Thank you.

    I like the query for the sum but I would like a more flexible approach for the user filter. The users are often in flux but there are a set group. This set group is included on my table as a Combo Box, Value List but includes a "" option to allow ad hoc users to enter their info. If I set a query it won't be flexible enough to account for these ad hoc users.

    I like the coding for the date filter and was hoping for something similar for the user filter.

    Thanks again!
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Allen Browne has used a Report Filter I tend to use Query Filters as they are more flexible.
    You can Filter the Query using the values from the Form.
     
  7. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Is there a way to allow the users to set the filters without accessing the query though? I just want them to press a button, enter their name & date range and get a result. I do not want them to have to run a query (it's a stretch getting them to fill out a pre-made Access Form). I'm not sure how to set a query that doesn't require the user to set their parameters as part of building the query. How would I do this?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    When a Report is based on a Query the query is automatically run when you open the report, so the button only has to open the report.
    You use form fields and use that as the Criteria using the Format
    forms![formname]![fieldname]
    in the Criteria Row, where formname is the name of the form and fieldname is the name of a field containing the required data.
    If it is between 2 dates you use
    Between forms![formname]![fieldname1] And forms![formname]![fieldname2]
    which contain the dates.
     
  9. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Thank you again for helping but this.

    I understand the report will link to the query and I can set my fields on the report. However, my trouble is the Criteria will constantly be changing so I can't set the between criteria upfront. A user may need to pull entry info between January-May 2012 or all of January 2013 or just a few days in February. This is my trouble, I can't preset the search criteria since it will always be changing.

    If that's possible with what you suggest perhaps I'm just missing it. Can you give me a sample report perhaps? I need the results separated first by User, then by date range where the range can be entered by the user at the time they run the report, then proved the results of the query showing the Sum of Line Items.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you need to have the User and the dates as criteria in the same query or just one of them?
    The dates would be entered by the user in the form, so they can be a few days, a few months or a few years.
     
  11. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    Ok dates perfect. Thank you. And I do needs the results displayed by user first, not sure if that means they'll have to on the query or not. But the users do link back to a combo box, value list on my table. The users can enter names not preset onthe list though.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If the user one is a different query to the Dates one that will be fine, otherwise it needs a field on the form to say which Criteria to use.
     
  13. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    I have a Table that has the dates and the User Names. There is only one Table. I'm not sure what this means in terms of how to set up the Query as I still don't quite understand how a query will work for my purposes in terms of filtering by User and Date Range.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you provide me with a copy of the database with some dummy data in the Table for me show you how it works?
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083595

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice