Sort with Nulls at End

[QuadSquad]

I have a table that I am trying to use a query to sort by Phone Number. Some
of the records have no phone number, when I sort ascending it gives me the
records with no phone number first and then the populated ones. I would like
to have it sorted with the nulls at the bottom. I tried putting this

IIf(IsNull([CustomerPhone1]),"(999) 999-9999",[CustomerPhone1])

in my Criteria field but when I run the query it completely leaves out any
of the records with a blank phone number and just gives me the populated
ones. What am I doing wrong?

Thanks.
Arica

 

[Jerry Whittle]

Put it in a field on top and not in the criteria. Also make sure to sort on
that field. If you don't want to display the 999's, uncheck the display box.
You can still sort on it even if not shown in a query. However if the query
is going to drive a report, you'll need it for the sorting and grouping
options in the report. However the field could be invisible.

 

[John Spencer]

The expression should not be a criteria. It should be put in as a
calculated field and then sort on the calculated field. You don't have to
show the field.

Another option would be to use a calculated field and sort by it and then by
the phone number
Field: NoPhone: CustomerPhone1 is Null
Sort: Descending

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..