I have an application with many names that need filtering to become manageable.
I have an option box with a sorting macro for initial letters which is all right but there are certain names that are extremely numerous and would be better sorted separately - like Smith for instance.
The macro now simply states "apply filter" where name "Like S*" and filters by the initial.
How would I exclude "Smith" in this coding? Your help appreciated?
Thanks, Roy.
sorting option box by macro
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 70
- Joined: 24 Dec 2012, 20:26
Re: sorting option box by macro
Sorrry, Hans, that code didn't do the trick. It still sorted on initial letter. I've tried other similar codes but couldn't get it to work.
Ought to be more straightforward I would have thought.
Thanks, Roy.
Ought to be more straightforward I would have thought.
Thanks, Roy.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: sorting option box by macro
In your opening post, you refer to filtering but in your second post you refer to sorting?
Can you explain more clearly what you are doing?
In a quick test I can confirm that the filter that Hans mentions provides correct results; extracting all names starting with S (excluding Smith)
Can you explain more clearly what you are doing?
In a quick test I can confirm that the filter that Hans mentions provides correct results; extracting all names starting with S (excluding Smith)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- StarLounger
- Posts: 70
- Joined: 24 Dec 2012, 20:26
Re: sorting option box by macro
I'll try it again and get back to you later, thanks Roy.
-
- StarLounger
- Posts: 70
- Joined: 24 Dec 2012, 20:26
Re: sorting option box by macro
I tried again but still just got sorting and filtering on initial letters.
The data is sorted by the macro's "where" condition fr each option and the displaying form is filtered by the macro's action "apply filter".
I cannot see why the exceptional condition as per "Smith" is not working.
At present there are 27 option choices for each command button i.e. A through Z in English and one for "ALL".
Adding "Smith" as an option, for example, might be say option 28, and so on for any other choices like "Jones", "Williams", etc.
I will re-examine this again because I might be overlooking something very simple.
Thanks for your assistance.
The data is sorted by the macro's "where" condition fr each option and the displaying form is filtered by the macro's action "apply filter".
I cannot see why the exceptional condition as per "Smith" is not working.
At present there are 27 option choices for each command button i.e. A through Z in English and one for "ALL".
Adding "Smith" as an option, for example, might be say option 28, and so on for any other choices like "Jones", "Williams", etc.
I will re-examine this again because I might be overlooking something very simple.
Thanks for your assistance.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: sorting option box by macro
Can you tell us whether you want to sort or to filter?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 70
- Joined: 24 Dec 2012, 20:26
Re: sorting option box by macro
The macro does both.
The data is sorted by the macro's "where" condition for each option and the displaying form is filtered by the macro's action "apply filter".
The form is filtered based upon the sorting associated with the selected option command button in the option box.
Thanks, Roy
The data is sorted by the macro's "where" condition for each option and the displaying form is filtered by the macro's action "apply filter".
The form is filtered based upon the sorting associated with the selected option command button in the option box.
Thanks, Roy
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: sorting option box by macro
The expression
[Name] Like 'S*' And [Name]<>'Smith'
is intended to be used as where-condition for filtering the names that begin with "S", except "Smith". It should work for that purpose:
but I don't see how it could be used for sorting the data.
[Name] Like 'S*' And [Name]<>'Smith'
is intended to be used as where-condition for filtering the names that begin with "S", except "Smith". It should work for that purpose:
Code: Select all
Private Sub cmdS_Click()
Me.Filter = "[Name] Like 'S*' And [Name]<>'Smith'"
Me.FilterOn = True
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 70
- Joined: 24 Dec 2012, 20:26
Re: sorting option box by macro
All right. I'll look it up on Microsoft. Thanks, Roy.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: sorting option box by macro
Perhaps we could help you better if you explained what you mean by sorting here.
Best wishes,
Hans
Hans