how can I sort nulls to the bottom

[Guest]

I have a form based on a query. I want to sort my patient records ascending
based on the fields NextAppt (date) then ApptTime. The probem is that some
patients do NOT have a NextAppt scheduled and are null values. I need the
null values to sort to the END not the beginning (Alphbetical by LastName,
FirstName if possible). Is there an EASY way to sort nulls to the end? I'm
very new to ACCESS.

 

[Armen Stein]

In your query, create a new field:
Expr1: IsNull([NextAppt])
Specify Descending sort.

Drag it so that it is between your Name columns and your ApptDate
columns.

Those Nulls will drop to the bottom of each Patient.

I have a form based on a query. I want to sort my patient records ascending
based on the fields NextAppt (date) then ApptTime. The probem is that some
patients do NOT have a NextAppt scheduled and are null values. I need the
null values to sort to the END not the beginning (Alphbetical by LastName,
FirstName if possible). Is there an EASY way to sort nulls to the end? I'm
very new to ACCESS.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Duane Hookom]

You can create a column with the expression:
SortMe: Nz(NextAppt,#12/31/2222#)

 

[Guest]

This Expr1 phrase worked as far as sorting my nulls in the NextAppt field to
the bottom of my Query. The query table is sorted correctly now. But I want
this order to transfer to my form based on the query (PatientAppts Query)...
ie each patient has a page in the form and I want each patient page to come
in order of NextAppt then ApptTime then nulls. When I open my form
(PatientAppointments) after running the query it's pages are still ordered
with the nulls first.

In your query, create a new field:
Expr1: IsNull([NextAppt])
Specify Descending sort.

Drag it so that it is between your Name columns and your ApptDate
columns.

Those Nulls will drop to the bottom of each Patient.

I have a form based on a query. I want to sort my patient records ascending
based on the fields NextAppt (date) then ApptTime. The probem is that some
patients do NOT have a NextAppt scheduled and are null values. I need the
null values to sort to the END not the beginning (Alphbetical by LastName,
FirstName if possible). Is there an EASY way to sort nulls to the end? I'm
very new to ACCESS.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Guest]

Couldn't get this to work, not sure what I'm doing wrong. I created the
column in the design view of my query and sorted ascending.

 

[Armen Stein]

You need to use this technique in the query the form is based on.

Your reply makes me think that you expect that running the query once
will change the order of the records the next time you open the form.
Queries don't do that. They return records in the order you specify
only when they run.

Also, in the Order By property of your form, make sure that you aren't
specifying a different order that would override the one from your
query.

This Expr1 phrase worked as far as sorting my nulls in the NextAppt field to
the bottom of my Query. The query table is sorted correctly now. But I want
this order to transfer to my form based on the query (PatientAppts Query)...
ie each patient has a page in the form and I want each patient page to come
in order of NextAppt then ApptTime then nulls. When I open my form
(PatientAppointments) after running the query it's pages are still ordered
with the nulls first.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Guest]

Armen, I solved my sorting problem. I had first tried an advanced filter on
my form and it was still on. When I removed the filter my form came up just
as I wanted. THANK YOU!! I appreciate your help. Debbi

In your query, create a new field:
Expr1: IsNull([NextAppt])
Specify Descending sort.

Drag it so that it is between your Name columns and your ApptDate
columns.

Those Nulls will drop to the bottom of each Patient.

I have a form based on a query. I want to sort my patient records ascending
based on the fields NextAppt (date) then ApptTime. The probem is that some
patients do NOT have a NextAppt scheduled and are null values. I need the
null values to sort to the END not the beginning (Alphbetical by LastName,
FirstName if possible). Is there an EASY way to sort nulls to the end? I'm
very new to ACCESS.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com