Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    267

    Unanswered: Changing the Query Sort order using VBA

    I have got a query, that is fairly complex. It works nicely, but the users want to chose which field to sort on prior to displaying it (about 15 different possibilities). Because of the complexity of it and because it is the underlying query to several reports, I would like to be able to change the sort order to this query prior to displaying it to the user, using VBA. Is it possible to do this, or is there any other options that I haven't thought of? The only other option that I can think of is to just make multiple queries (that are identical, outside of the sort order) and display the one that corresponds to the sort order they want.

    Thanks

    S-

  2. #2
    Join Date
    Feb 2004
    Posts
    6
    You can copy the query SQL and use it in an if-then-else or a Select Case statement . Change the sql for each sort order then execute it via the docmd.runsql action for each case

    Select Case sortOrder

    Case 1
    docmd.runsql "SELECT field FROM table ORDERBY orderCriteria1"

    Case 2
    docmd.runsql "SELECT field FROM table ORDERBY orderCriteria2"

    Case 3
    docmd.runsql "SELECT field FROM table ORDERBY orderCriteria3"

    etc...

    End Select

    Hope this Helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •