Sorting empty fields

  • Thread starter Thread starter Hank
  • Start date Start date
H

Hank

My form has the following fields: ID, DueDate, DatePaid,HowPaid,Check#,
AmtPaid, FeePaid, Gift, PastDue, Notes.

A DatePaid entry can be made for a Gift or Fee leaving the duedate field
empty. I would like to sort by the due date with the empty fields sorted by
datepaid. Right now the empty fields are on top of the sorted list. I do
not want to sort by datepaid when duedate field is not empty.
 
My form has the following fields: ID, DueDate, DatePaid,HowPaid,Check#,
AmtPaid, FeePaid, Gift, PastDue, Notes.

A DatePaid entry can be made for a Gift or Fee leaving the duedate field
empty. I would like to sort by the due date with the empty fields sorted by
datepaid. Right now the empty fields are on top of the sorted list. I do
not want to sort by datepaid when duedate field is not empty.
Click to expand...

Try putting a calculated field in the query:

SortBy: NZ(DueDate, DatePaid)

and sort by this field. It'll sort by DueDate if there is one, and only if it
is NULL by DatePaid. Of course if both fields are null the result will be null
and it will sort at the top; to sort these at the bottom you could use

NZ(NZ(DueDate, DatePaid), #12/31/9999#)
 
Back
Top