Results 1 to 2 of 2
  1. #1
    TonyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Oxfordshire, UK
    Posts
    16

    Question Ordering data based on an iif statement?

    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

  2. #2
    TonyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Oxfordshire, UK
    Posts
    16
    Feel like a complete idiot, this was so easy to solve!
    All I did was add the iif statement above into the Order By property on the form and remove the "="!.

    So : IIf(IsNull([date2]),[date],[date2])

    Works perfectly!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ordering Data within a form
    By system243trd in forum Forms
    Replies: 1
    Last Post: 12-02-2011, 12:36 PM
  2. If Statement based on Combo Box selection
    By Jojojo in forum Programming
    Replies: 2
    Last Post: 11-02-2011, 05:42 PM
  3. Ordering data from a query
    By kulanga in forum Queries
    Replies: 9
    Last Post: 03-24-2010, 08:16 AM
  4. Replies: 5
    Last Post: 06-30-2009, 09:30 AM
  5. Replies: 1
    Last Post: 08-04-2008, 03:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums