Results 1 to 3 of 3
  1. #1
    Smitoris is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Posts
    6

    Sort/Order By Two Fields Based on Iif/Choose/Switch

    I've been searching forums for hours and trying different variations of functions to no avail...



    I'm trying to sort a query on two fields based on a parameter selection from another query. These two fields change based on this parameter. I tried manipulating the Order By SQL statement using the Choose function (based on the "index" number returned by qryRange.Division). It looked something like this:

    Order By Choose([qryRange.Division], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand]);

    where indexes 4 and 7 return a sort using Department/Program and the remaining indexes return a sort using Program/Brand.

    When attempting to run the query I received an error message stating that the query in which I added the choose statement is invalid due to a character (or something to that effect). I futzed around with the parantheses and brackets and received other errors having to do with commas, etc... Just couldn't get it to work.

    I then attempted to nest two Iif functions after the Order By statement. Translated into English it said, if qryRange.Division = 4 or 7 then [Element.Department, Element.Program], otherwise [Element.Program, Element.Brand]. This also did not work.

    Please help. Let me know if you need to see any data/SQL.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you doing this in an Access query or in VBA? Never tried these before. Don't know if will work in ORDER BY clause.

    The brackets indicate a single name.

    ORDER BY Choose([qryRange.Division], Element.Program & Element.Brand, Element.Program & Element.Brand, Element.Program & Element.Brand, Element.Department & Element.Program, Element.Program & Element.Brand, Element.Program & Element.Brand, Element.Department & Element.Program, Element.Program & Element.Brand, Element.Program & Element.Brand);

    or

    ORDER BY IIf(Division = 4 Or Division = 7, Department & Program, Program & Brand)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Smitoris is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Posts
    6
    Wonderful. The ampersand worked like a charm. Thanks for the help.

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

Similar Threads

  1. Multivalue field sort order
    By bdaun in forum Forms
    Replies: 0
    Last Post: 09-05-2011, 12:27 PM
  2. Replies: 1
    Last Post: 07-24-2010, 07:46 AM
  3. Paradox Sort order for linking tables to access
    By Jimmy in forum Import/Export Data
    Replies: 0
    Last Post: 02-02-2010, 03:13 AM
  4. Sort Order Variation with nulls
    By jonsuns7 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 03:10 AM
  5. Access 2003, sort order property of a form
    By Rick West in forum Forms
    Replies: 11
    Last Post: 09-17-2009, 08:28 PM

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