By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Allen Browne form filter top n records?

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Option Explicit
  4.  
  5. Private Sub btnSearchit_Click()
  6.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  7.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  8.                     we remove the trailing " AND " at the end.
  9.     '           2. The date range works like this: _
  10.                         Both dates      = only dates between (both inclusive. _
  11.                         Start date only = all dates from this one onwards; _
  12.                         End date only   = all dates up to (and including this one).
  13.  Dim strWhere2 As String                  'The criteria string.
  14.     Dim lngLen2 As Long                      'Length of the criteria string to append to.
  15.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  16.  
  17. '***********************************************************************
  18. 'Begin the filter codes to create the query
  19. '***********************************************************************
  20.  
  21.  
  22. '------------------------------------------------
  23. 'Begin coding for date search of date occured
  24. '------------------------------------------------
  25.  
  26. 'Date field example. Use the format string to add the # delimiters and get the right international format.
  27.     If Not IsNull(Me.txtfilterDateStart) Then
  28.         strWhere2 = strWhere2 & "([DteOccur] >= " & Format(Me.txtfilterDateStart, conJetDate) & ") AND "
  29.     End If
  30.  
  31.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  32.     If Not IsNull(Me.txtfilterDateEnd) Then   'Less than the next day.
  33.         strWhere2 = strWhere2 & "([DteOccur] < " & Format(Me.txtfilterDateEnd + 1, conJetDate) & ") AND "
  34.     End If
  35.  
  36.  
  37.  
  38. '------------------------------------------------
  39. ' End coding for date searching of date occured
  40. '------------------------------------------------
  41.  
  42. '------------------------------------------------
  43. ' Start coding for list box to see department raised
  44. '------------------------------------------------
  45.  
  46. 'Listbox example.
  47.     If Me.lstfilterDeptRaised.ItemsSelected.Count > 0 Then
  48.         Dim itm As Variant
  49.         Dim strList As String
  50.         For Each itm In Me.lstfilterDeptRaised.ItemsSelected
  51.             If strList = "" Then
  52.                 strList = "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
  53.             Else
  54.                 strList = strList & " OR " & "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
  55.             End If
  56.         Next
  57.         strWhere2 = strWhere2 & "(" & strList & ") AND "
  58.     End If
  59.  
  60. '------------------------------------------------
  61. ' End coding for list box to see department raised
  62. '------------------------------------------------
  63.  
  64.  
  65.  
  66. '------------------------------------------------
  67. ' Start coding for list box to see department responsible
  68. '------------------------------------------------
  69.  
  70. 'Listbox example.
  71.     If Me.lstfilterDeptResp.ItemsSelected.Count > 0 Then
  72.         Dim itm2 As Variant
  73.         Dim strList2 As String
  74.         For Each itm2 In Me.lstfilterDeptResp.ItemsSelected
  75.             If strList2 = "" Then
  76.                 strList2 = "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
  77.             Else
  78.                 strList2 = strList2 & " OR " & "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
  79.             End If
  80.         Next
  81.         strWhere2 = strWhere2 & "(" & strList2 & ") AND "
  82.     End If
  83.  
  84. '------------------------------------------------
  85. ' End coding for list box to see department responsible
  86. '------------------------------------------------
  87.  
  88.  
  89.      '***********************************************************************
  90.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  91.     '***********************************************************************
  92.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  93.     lngLen2 = Len(strWhere2) - 5
  94.     If lngLen2 <= 0 Then     'Nah: there was nothing in the string.
  95.         MsgBox "No criteria", vbInformation, "Nothing to do."
  96.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  97.         strWhere2 = Left$(strWhere2, lngLen2)
  98.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  99.         'Debug.Print strWhere2
  100.  
  101.         'Finally, apply the string as the form's Filter.
  102.         Forms!frmlogforfiltering.Form.Filter = strWhere2
  103.         Forms!frmlogforfiltering.Form.FilterOn = True
  104.     End If
  105. End Sub
  106.  
  107. Private Sub btnResetit_Click()
  108.     'Purpose: Clear all the search boxes in the Form Header, and show all records again.
  109.     Dim ctl As Control
  110.  
  111.     'Clear all the controls in the Form Header section.
  112.     For Each ctl In Me.Section(acHeader).Controls
  113.         Select Case ctl.ControlType
  114.         Case acTextBox, acComboBox
  115.             ctl.Value = Null
  116.         Case acCheckBox
  117.             ctl.Value = False
  118.         End Select
  119.     Next
  120.  
  121.     'Remove the form's filter.
  122.     Me.Filter = "(False)"
  123.     Me.FilterOn = True
  124. End Sub
  125.  
  126. Private Sub Form_BeforeInsert(Cancel As Integer)
  127.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  128.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  129.     'The problems are explained at http://allenbrowne.com/bug-06.html
  130.     Cancel = True
  131.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  132. End Sub
  133.  
  134. Private Sub Form_Open(Cancel As Integer)
  135.     'Remove the single quote from these lines if you want to initially show no records.
  136.     Me.Filter = "(False)"
  137.     Me.FilterOn = True
  138. End Sub
  139.  
  140.  
Jul 5 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3
  2.          [Score]
  3.         ,[Name]
  4. FROM     [Results]
  5. ORDER BY [Score] ASC
Table=[Results]
Expand|Select|Wrap|Line Numbers
  1. [Score]  [Name]
  2.    5     Angela
  3.    2     Bob
  4.    2     Charlie
  5.    1     Denise
  6.    2     Eric
The results for this query would be :
Expand|Select|Wrap|Line Numbers
  1. 1, Denise
  2. 2, Charlie
  3. 2, Bob
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3
  2.          [Score]
  3.         ,[Name]
  4. FROM     [Results]
  5. ORDER BY [Score] ASC
  6.         ,[Name] ASC
The results for this query would be :
Expand|Select|Wrap|Line Numbers
  1. 1, Denise
  2. 2, Bob
  3. 2, Charlie

Share this Question
Share on Google+
7 Replies


FishVal
Expert 2.5K+
P: 2,653
You should assign SQL statement returning desired recordset to Form.RecordSource 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
Jul 5 '10 #2

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


Expand|Select|Wrap|Line Numbers
  1. 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
Jul 5 '10 #3

NeoPa
Expert Mod 15k+
P: 31,433
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3
  2.          [Score]
  3.         ,[Name]
  4. FROM     [Results]
  5. ORDER BY [Score] ASC
Table=[Results]
Expand|Select|Wrap|Line Numbers
  1. [Score]  [Name]
  2.    5     Angela
  3.    2     Bob
  4.    2     Charlie
  5.    1     Denise
  6.    2     Eric
The results for this query would be :
Expand|Select|Wrap|Line Numbers
  1. 1, Denise
  2. 2, Charlie
  3. 2, Bob
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3
  2.          [Score]
  3.         ,[Name]
  4. FROM     [Results]
  5. ORDER BY [Score] ASC
  6.         ,[Name] ASC
The results for this query would be :
Expand|Select|Wrap|Line Numbers
  1. 1, Denise
  2. 2, Bob
  3. 2, Charlie
Jul 5 '10 #4

NeoPa
Expert Mod 15k+
P: 31,433
munkee:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 5 '10 #5

100+
P: 374
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
Jul 5 '10 #6

NeoPa
Expert Mod 15k+
P: 31,433
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.
Jul 5 '10 #7

100+
P: 374
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.

Expand|Select|Wrap|Line Numbers
  1. '------------------------------------------------
  2. ' Start coding to be able to select the number of records to display
  3. '------------------------------------------------
  4. Dim topN As String
  5. Dim topinsert As String
  6.  
  7. topN = comboNrecords.Value
  8.  
  9. If topN = "All" Then
  10. topinsert = "All"
  11.  
  12. Else
  13.  
  14. topinsert = "TOP " & topN
  15.  
  16. End If
  17.  
  18. '------------------------------------------------
  19. ' End coding to be able to select the number of records to display
  20. '------------------------------------------------
  21.  
  22.  
  23.  
  24.         'Finally, apply the string as the form's Filter.
  25.         'Me.RecordSource = "Select * FROM logforfiltering WHERE " & strWhere2
  26.        Me.RecordSource = "Select " & topinsert & " * FROM logforfiltering WHERE " & strWhere2
  27.        ' Forms!frmlogforfiltering.Form.FilterOn = True
Jul 5 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.