query records by numbers column with blank records showing last

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to query the numbers column to show my records in numeric order. By
default it shows the records without a number assigned, first. I want those
records to be displayed at the end.
 
JMT203 said:
I want to query the numbers column to show my records in numeric order. By
default it shows the records without a number assigned, first. I want those
records to be displayed at the end.
Click to expand...


Add a calculated column to the query

Sorting: Nz(numbers, 999999999)

Set it to sort Ascending and uncheck the Show box.
 
Order by [TheField] is Null Desc, [TheField]

If you are doing this in the query grid, add a calculated column. In front of
the column Number column if you are using the grid.

FIeld: [TheTable].[TheField] is Null
Sort By: descending
Show : Unchecked

An alternative, if your numbers column has a known maximum is to sort by NZ([TheNumberField],10000000000)
That assumes that 10000000000 is larger than any number you have in the number
field (and it will fail to sort property for you if you ever do get the larger number.
 
Back
Top