Sort certain records to top of list

[Allison]

Access 2003, Win XP

I need to be able to sort records that contain an asterisk (*) to the top of
a list that's already sorted alphabetically.

The field which contains the asterisk is "fldFirstName". Properly, there
should be some other way to designate these records, but this was built by
people who didn't think we'd need this function.

Is it possible to check the filed for an asterisk, then put it at the top of
the list but keep all the non-asterisk records sorted alphabetically? If so,
how would I go about doing that?

Thanks for your help.
Allison

 

[Klatuu]

Use the ORDER BY clause of your query. Order by the fldFirstName in
descending order so that the * will sort ahead of those with spaces.

ORDER BY fldFirstName DESC, OtherFieldName ASC

 

[Allison]

Sorry, I should have clarified. The default alphabetic sort is by a field
called fldLastName. If I change this to sort by fldFirstName, that will mess
up the fldLastName sort.

I want it to do both, with the asterisk trumping the last name sort, but
only for that record.

So, if I have this without the "asterisk trumping" sort:

Bessie Bell
* George Klum
Aaron Wagner

I want it to sort like this:

* George Klum
Bessie Bell
Aaron Wagner

 

[Keith Wilby]

Access 2003, Win XP

I need to be able to sort records that contain an asterisk (*) to the top
of
a list that's already sorted alphabetically.

The field which contains the asterisk is "fldFirstName". Properly, there
should be some other way to designate these records, but this was built by
people who didn't think we'd need this function.

Is it possible to check the filed for an asterisk, then put it at the top
of
the list but keep all the non-asterisk records sorted alphabetically? If
so,
how would I go about doing that?

Thanks for your help.
Allison

Have you tried a union query with the first part filtering for the asterisk
and the second part filtering for the rest?

Keith.
www.keithwilby.co.uk

 

[KARL DEWEY]

Use a calculated field to sort like this --
Sort_Field: IIF(Asc([fldFirstName]) = 42, 0, 1) & [fldLastName]

An asterisk is ASCII code 42 to give a zero to sort first.

 

[Klatuu]

If you are saying the fldFirstName will sometimes be a name and sometimes an
* then, yes, it will mess it up. Don't try to use the same field for two
different meanings.

 

[Allison]

Thanks Karl; this worked perfectly.

Allison

Use a calculated field to sort like this --
Sort_Field: IIF(Asc([fldFirstName]) = 42, 0, 1) & [fldLastName]

An asterisk is ASCII code 42 to give a zero to sort first.

--
Build a little, test a little.

Sorry, I should have clarified. The default alphabetic sort is by a field
called fldLastName. If I change this to sort by fldFirstName, that will mess
up the fldLastName sort.

I want it to do both, with the asterisk trumping the last name sort, but
only for that record.

So, if I have this without the "asterisk trumping" sort:

Bessie Bell
* George Klum
Aaron Wagner

I want it to sort like this:

* George Klum
Bessie Bell
Aaron Wagner

 

[Allison]

One field, sometimes contains only a name, sometimes contains an asterisk and
a name. See earlier comment that I inherited it this way. Thanks for the
commentary though.

Allison

 

[Allison]

Thanks for that approach. Karl's answer helped me, but this would be a good
backup approach.

Allison

 

[Keith Wilby]

One field, sometimes contains only a name, sometimes contains an asterisk
and
a name. See earlier comment that I inherited it this way. Thanks for the
commentary though.

Allison

I think Dave was trying to say that you are breaking a rule of normalisation
and that whatever the asterisk signifies should be stored in its own field.
This would make what you're trying to achieve much simpler.

Keith.
www.keithwilby.co.uk