blank fields sort

[sponge]

Hi,

Is there a way to get blank fields to sort to the bottom rather than the top when sorting alphabetically in access?

TIA!

 

[The_Doc_Man]

Not using normal methods.

You COULD generate a separate "sorting" field that you would use to provide an alternate sort order. Do a query to update the sort-order field with the count of records with lower values in the sort. You could then run a separate query to force all 9's in that field for blank records. After that, you could sort on the special sort ordering field.

However, there is also another "nasty" trick you could play.

Build a query that contains the fields you want plus one more YES/NO field. Make that field equal to an expression that is TRUE for non-blanks in the targeted field and FALSE for the case of all blanks in the targeted field.

Don't check the box that contains the YES/NO field. (i.e. Don't show it, just compute it) but DO sort the field in ascending order. Since YES is -1 and NO is 0, the blanks will sort last. Then assert ascending sort order in the targeted field to cover the stuff not sorted last because of the YES/NO field.

 

[sponge]

Many thanks for the reply.

I'll give those methods a shot and see how it goes.