sorting option box by macro

[royfarnol]

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.

[HansV]

Try

[Name] Like 'S*" And [Name]<>'Smith'

[royfarnol]

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.

[Rudi]

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)

[royfarnol]

I'll try it again and get back to you later, thanks Roy.

[royfarnol]

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.

[HansV]

Can you tell us whether you want to sort or to filter?

[royfarnol]

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

[HansV]

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:

Code: Select all

Private Sub cmdS_Click()
    Me.Filter = "[Name] Like 'S*' And [Name]<>'Smith'"
    Me.FilterOn = True
End Sub

but I don't see how it could be used for sorting the data.

[royfarnol]

All right. I'll look it up on Microsoft. Thanks, Roy.

[HansV]

Perhaps we could help you better if you explained what you mean by sorting here.