Results 1 to 5 of 5

Thread: Order By (2007)

  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Order By (2007)

    Want to Sort by a field and if the field is blank then all records with a blank field in the sort would be last. Now when I sort -> ORDER BY State, if the state is blank then all records without a state are listed first.

    Is it possible to reverse the listing and show those with a state at the top of the page and those without at the bottom?
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order By (2007)

    Let's say your field is named MyField.

    Create a query based on your table.
    Add the fields you want to display. Don't specify the sort order for them.
    To the right of the last populated column, created a calculated column:

    Not IsNull([MyField])

    Clear the Show check box for this column, and set the sort order to Ascending.
    To the right of this column, add MyField. (It's ok if you had already added it earlier).
    Clear the Show check box for this column, and set the sort order to Ascending.
    This query will show MyField in ascending order, but with blank values at the end.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Order By (2007)

    Hans,

    Thanks for the reply. Here is what I have:
    SELECT Lastname & ', ' & Firstname & IIf(Maidenname<>'',' (' & Maidenname & ')','') AS Name, Members.Address,
    IIF(City<>'',City & ' ,' & State & ' ' & Zip) AS CityStateZip
    FROM Members
    ORDER BY Not IsNull([State]), Members.State;

    If this is what you explained then I'm doing something wrong. Get the same results: records without states are first.
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order By (2007)

    Try changing it to
    <code>
    ...
    ORDER BY Not Trim(Nz([State],""))="", Members.State;</code>

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Order By (2007)

    Hans,

    That works in Access. This works in VB6: ORDER BY Not Trim(State=''), State
    Thanks

    Kim

Posting Permissions

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