User entered optional filters

[stevehoyle]

I'm new to Access. Powerful and relatively easy. I have 1 stumbling block to be able to complete this project. I want the user to easily enter optional parameters when using a form. i.e., there are multiple columns, I want the user to enter a value for each, with these possibilities: No entry, then retrieve everything for that column, If a column is to be filtered, I want the user to see a selection list, then can select one of the displayed values, or with check boxes select 2 or more values. Indicate if the selected values are to be included or excluded. I want it very easy for the user. A row of filter options at the top that are easily filled (or bypassed if no filtering desired), then the user hits a button (or something) to request the filtered data (no filters, partially filtered, everything filtered, whatever the user specified). Then I want the user to be able to go back and easily modify a filter (while keeping every other filter that isn't now modified) to see a new view of the data. This seems very reasonable to me, but I can't find anything that allows user entered filtering at the initial invoking of the form. Thanks in advance! P.S. I also want the filter for the dates to show a calendar selection for from and to, rather than as now which is just a box that the user has to fill with the from date, then another parameter box for the to date.

[June7]

One approach is parameterized query. Review: http://datapigtechnologies.com/flash...tomfilter.html

Other approaches require more code to construct filter string. Review: http://allenbrowne.com/ser-62.html

[stevehoyle]

One approach is parameterized query. Review: http://datapigtechnologies.com/flash...tomfilter.html

Other approaches require more code to construct filter string. Review: http://allenbrowne.com/ser-62.html

Thanks very much. This solves it. Allen Browne is exactly what I wanted. I'm a novice, so still trying to digest everything he is doing and then implementing it. Working on that now. Thanks again

[stevehoyle]

Thanks very much. This solves it. Allen Browne is exactly what I wanted. I'm a novice, so still trying to digest everything he is doing and then implementing it. Working on that now. Thanks again

I used Allen Browne’s ClientSearch form for entering criteria to filter the form detail display. I have got it working and it does build the proper strWhere based on the user entered parameters (which I can see in debug). However, the form then displays multiple “Enter Parameter Value box” in succession for GradeDate, StaffID, another box with the title of the value I entered for the StaffID parameter, and then asks for StudentID. These were all values which have already been entered into the parameter boxes in the form header. When I enter data again into these boxes then the process completes and no records are retrieved. Again, I verify that the where string is valid. I don’t understand the parameter boxes and I don’t see where the records are retrieved in Allen’s code after the strWhere is built. Any ideas? Thanks

[June7]

Would have to review your code. Post it or provide the file. See instructions at bottom of my post. Identify form involved.

[stevehoyle]

Would have to review your code. Post it or provide the file. See instructions at bottom of my post. Identify form involved.

OK, it only accepted max 500kb so I zipped it. The problem object is the form; frm_ViewGrades.

Thanks again.

Steve

[June7]

The VBA procedure is not using field names that are in the query:

Stdnt#
Staff ID
Date

Why did you use alias Date for GradeDate? Date is a reserved word and should not use reserved words as names.

Also, should not use spaces, special characters/punctuation (underscore is exception) in names.

Fix the query field names or fix the code or both, just get them in sync.

The student number is a text field so the criteria will need delimiters (Allen used doubled quote marks, I like apostrophe). The date values need # delimiters (Allen used the Format function and a custom constant to define a format parameter).

Basing criteria on staff initials is bound to fail. Multiple people can have same initials. However, again, this is text criteria, need delimiters.

Why don't you use comboboxes?

Consider (note the field names):

 If Not IsNull(Me.fltrFromDate) Or Not IsNull(Me.fltrToDate) Then
   strWhere = strWhere & "([GradeDate] BETWEEN #" & Nz(Me.fltrFromDate,"1/1/1900") & "# AND #" & Nz(Me.fltrToDate,"12/31/2900") & "#) AND "
 End If
 If Not IsNull(Me.fltrStaffID) Then
   strWhere = strWhere & "([Staff] = '" & Me.fltrStaffID & "') AND "
 End If
 If Not IsNull(Me.fltrStudentID) Then
   strWhere = strWhere & "([StdntNo] = '" & Me.fltrStudentID & "') AND "
 End If

[stevehoyle]

Thanks again, you have been very helpful. I was using different names for what I thought was the label that appeared on the form, not the fields in the DB. Anyway, I changed everything to always use the field names. I used apostrophe and # as delimiters. I used either the from date, to date or both because I want either a low limit, high limit, or range. I used to have the autonum as my relationship. When I was using the initials that was supposed to be something that the user related to but it went against the autonum field in the Staff DB. I agree about the intials but the client has used it for years, and they always maake the intials unique by using a third initial when necessary. I then went back and changed my relationships to Staff Initials and Student number since those must be unique anyway. However, I still kept the autonum. I made the changes as you suggested and it works! By the way, I do want a combobox as you suggested, I just started with Allens code as is. I want the combo box to offer a selection list and user can select 1, severl, or all except 1 or a couple. I'll work on that next. In the meantime I copied this working form (for getting each grade) to a new form (for getting averages). The selection filter logic is identical. I changed the new form to use the different query that does the grouping for averages. However, this new form is not accepting the parameters. I've studied it but don't see the problem. The parameter input is identical to the form that is now working. I've attached the code again, if you see my error that would be a great help. I keep looking and all I see is identical code that is working in the other form. Thanks again