Prev: Yes/No Format Issue
Next: SendKeys & Windows 7 Problem
From: Hank on 19 Apr 2010 21:32
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.
From: John W. Vinson on 19 Apr 2010 22:35
On Mon, 19 Apr 2010 18:32:01 -0700, Hank <Hank(a)discussions.microsoft.com>
wrote:

>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.

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#)
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Yes/No Format Issue
Next: SendKeys & Windows 7 Problem