Sorting

Topic

New Reply

Have a db (access) with all the usual profile fields such as last, first, address, city, state, zip and deceased. Some fields will have no data like address, city, state, zip and deceased. Would like to sort by state with the blank fields at the end of the report.

This is as close as I’ve been able to get:

SELECT IIf([MaidenName]=” Or [Maidenname] Is Null,[Lastname] & ‘, ‘ & [firstname],[Lastname] & ‘, ‘ & [firstname] & ‘ (‘ & [Maidenname] & ‘)’) AS Name,
address, IIF(City”,City & ‘, ‘ & State & ‘ ‘ & Zip,”) AS CityStZip,
IIf([Deceased],’D’,”) AS Dead
FROM Members
ORDER BY iif(deceased is null, state,”)

Thanks for the help.

Reply | Quote

Replies

Hi Kim –

Don’t know if this is what you’re after, but is based on the fact that, with Yes/No or True/False fields, you can sort ascending or descending directly on that field.

I read it that you want all the not dead people sorted by state, if they have one. I’m not clear what you want to do with the dead people, but you should be able to adapt this accordingly. I just created a field based on whether or not the state was null, and sorted on that field. You could adapt it further if you thought there might be zero-length rather than null strings for some of the states. But the basic SQL is:

SELECT Table1.ysnDead, Table1.strState, Table1.strName
FROM Table1
ORDER BY Not (IsNull([strState])), Table1.ysnDead DESC , Table1.strState, Table1.strName;

This sorts first by state, putting the nulls at the bottom of the list. Then by dead-or-alive, putting the live ones above the dead ones, before those with no state listing. Then by state then name within those first groups.

If I’m on the right track for what you want, but you need a bit more information, let me know and I’ll try to clarify/expand on this.

Cheers

Alison C

Reply | Quote

You’re not only on the right track, that is a bulls eye.

How to Thank?

Reply | Quote

The bulls-eye comment is thanks enough!

Cheers

Alison

Reply | Quote