Allen Browne form filter top n records?

[munkee]

Hi all,

I am using Allen's excellent form filter script to filter the results from a query.

I would now like to add some further functionality. How do I go about displaying say the top N costs where in my search form I would have a drop down to select the N value and use radio buttons to select the field the top N should be run against (e.g. cost).

Thanks for any help in advance my code is as follows:

Option Compare Database
 
Option Explicit
 
Private Sub btnSearchit_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
 Dim strWhere2 As String                  'The criteria string.
    Dim lngLen2 As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
'***********************************************************************
'Begin the filter codes to create the query
'***********************************************************************
 
 
'------------------------------------------------
'Begin coding for date search of date occured
'------------------------------------------------
 
'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtfilterDateStart) Then
        strWhere2 = strWhere2 & "([DteOccur] >= " & Format(Me.txtfilterDateStart, conJetDate) & ") AND "
    End If
 
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtfilterDateEnd) Then   'Less than the next day.
        strWhere2 = strWhere2 & "([DteOccur] < " & Format(Me.txtfilterDateEnd + 1, conJetDate) & ") AND "
    End If
 
 
 
'------------------------------------------------
' End coding for date searching of date occured
'------------------------------------------------
 
'------------------------------------------------
' Start coding for list box to see department raised
'------------------------------------------------
 
'Listbox example.
    If Me.lstfilterDeptRaised.ItemsSelected.Count > 0 Then
        Dim itm As Variant
        Dim strList As String
        For Each itm In Me.lstfilterDeptRaised.ItemsSelected
            If strList = "" Then
                strList = "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
            Else
                strList = strList & " OR " & "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
            End If
        Next
        strWhere2 = strWhere2 & "(" & strList & ") AND "
    End If
 
'------------------------------------------------
' End coding for list box to see department raised
'------------------------------------------------
 
 
 
'------------------------------------------------
' Start coding for list box to see department responsible
'------------------------------------------------
 
'Listbox example.
    If Me.lstfilterDeptResp.ItemsSelected.Count > 0 Then
        Dim itm2 As Variant
        Dim strList2 As String
        For Each itm2 In Me.lstfilterDeptResp.ItemsSelected
            If strList2 = "" Then
                strList2 = "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
            Else
                strList2 = strList2 & " OR " & "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
            End If
        Next
        strWhere2 = strWhere2 & "(" & strList2 & ") AND "
    End If
 
'------------------------------------------------
' End coding for list box to see department responsible
'------------------------------------------------
 
 
     '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen2 = Len(strWhere2) - 5
    If lngLen2 <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere2 = Left$(strWhere2, lngLen2)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere2
 
        'Finally, apply the string as the form's Filter.
        Forms!frmlogforfiltering.Form.Filter = strWhere2
        Forms!frmlogforfiltering.Form.FilterOn = True
    End If
End Sub
 
Private Sub btnResetit_Click()
    'Purpose: Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
 
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
 
    'Remove the form's filter.
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub
 
Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub
 
 

[FishVal]

You should assign SQL statement returning desired recordset to Form.RecordSour ce property, instead of assigning filter expression to Form.Filter property. To make the SQL expression to return a desired quantity of top records use "TOP n" predicate.

Regards,
Fish

[munkee]

Updated: managed to put in the recorsource change.. if you viewed this before the edit I must have been having a retarded mind block.

Me.RecordSource = "Select * FROM logforfiltering WHERE" & strWhere2

I can just apply the TOP N clause in there then now I assume.

Thanks for the reply Fish

[NeoPa]

This is not about a filter (which is essentially fully defined within the WHERE clause). This is about the SELECT clause of a SQL query.

If you are still talking about a subform (I had to reverse engineer to work this out. Please consider providing the information in future questions) then you would need the .RecordSource of the subform to be in SQL format. It's possible to use a QueryDef as the .RecordSource of a form, but if so, you would need to get the SQL from it and amend that before setting the .RecordSource to the resultant SQL.

Be that as it may, when you have the SQL you have to work on, you first need to check it doesn't already have the TOP predicate (of the SELECT clause) set. If so then it needs to be removed before adding your own. Adding this predicate is simple enough. You insert it into the existing SQL string at position 8 (after "SELECT "). The TOP predicate comes with an option to include the word PERCENT, which converts the value used to a percentage instead of absolute value.

NB. In sorted recordsets the Nth item may have equivalents. In this case all equivalents are included.
EG.

SELECT TOP 3
         [Score]
        ,[Name]
FROM     [Results]
ORDER BY [Score] ASC

Table=[Results]

[Score]  [Name]
   5     Angela
   2     Bob
   2     Charlie
   1     Denise
   2     Eric

The results for this query would be :

1, Denise
2, Charlie
2, Bob
2, Eric

Even though the query said TOP 3, 4 records resulted.

If it's important to avoid this then consider including some form of unique value in the SORT BY clause.
EG.

SELECT TOP 3
         [Score]
        ,[Name]
FROM     [Results]
ORDER BY [Score] ASC
        ,[Name] ASC

The results for this query would be :

1, Denise
2, Bob
2, Charlie

[NeoPa]

munkee:

Me.RecordSource = "Select * FROM logforfiltering WHERE" & strWhere2

I can just apply the TOP N clause in there then now I assume.

Yes, but you may want to add a space after "WHERE".

Personally I'd keep the filter away from the .RecordSource. That way the operator is told when the form is filtered and when not. I also find it more consistent, but it's your call of course.

[munkee]

Thanks for all the info NeoPa and for the initial post Fish . I will give this a go now but it seems pretty straight forward now from the posts, thank you also for the help if the select starts returning more values than I wanted in my top N.. great stuff

[NeoPa]

It's a pleasure :) I'm glad it helped.

If I may though, I'll switch the Best Answer posts as there's little of help for a new reader in the last one. Most of the information is in the previous one.

[munkee]

No problem NeoPA I will also post the following code:

This allows the user to select a value for the TOP N records to show from a combo box. In my combo box (comboNrecords) I have set values of 1 , 5 , 10 , 100 , All . When you select from the combo box it will change the TOP N for the records to the selected. Working really well to be honest.

'------------------------------------------------
' Start coding to be able to select the number of records to display
'------------------------------------------------
Dim topN As String
Dim topinsert As String
 
topN = comboNrecords.Value
 
If topN = "All" Then
topinsert = "All"
 
Else
 
topinsert = "TOP " & topN
 
End If
 
'------------------------------------------------
' End coding to be able to select the number of records to display
'------------------------------------------------
 
 
 
        'Finally, apply the string as the form's Filter.
        'Me.RecordSource = "Select * FROM logforfiltering WHERE " & strWhere2
       Me.RecordSource = "Select " & topinsert & " * FROM logforfiltering WHERE " & strWhere2
       ' Forms!frmlogforfiltering.Form.FilterOn = True