Hi,
I am trying to sort dates in a particular way, but it is not as straight forward as it sounds. Rather than try and explain, it’s easier for me to give you an example of what I’m trying to do:
The record set I have as my example are as follows:
Due date Actual date
([date]) ([date2])
14/07/2001
14/07/2011
14/07/2011 18/07/2011
06/07/2011 15/07/2011
12/05/2010
(sorry, the above's meant to be in two columns but cant draw tables on forums).
I have an “iif statement” on an unbound control box on the main form which means that if the actual date ([date2]) field for any records is null, it will display the due date ([date]), otherwise it will always show the actual date ([date2]):
=IIf(IsNull([date2]),[date],[date2])
So output on the main form is as follows (with “date due” as the priority sort order):
Date:
14/07/2011
14/07/2011
18/07/2011
15/07/2011
12/05/2011
This is what I want, and works great but it doesn’t then consider the ordering of the dates.
I know why it’s sorting like it does above (because I’ve told it to sort by due date ([date])). But I want it to sort like this:
Date:
18/07/2011
15/07/2011
14/07/2011
14/07/2011
12/05/2011
I can’t give “priority” to either the due or actual date fields because this would mean that it would list all of one, then the other. So therefore I’m sure this must mean that I need to put an “iif statement” into the Order By field of the form properties, but I don’t know how (or even if this is possible in Access).
Please help! Thanks