Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    New Jeruz
    Posts
    3

    Unanswered: ORDER BY statement

    MS Access 2002
    SQL = "Select * from DomainsAndIPs Order By Service_Name ASC"

    I used the following query to sort results ascending, however in some records the Service_Name field is empty and those show up first. Is there a way to still sort alphabetically but have the records with the empty fields show up last?

    I then used this:
    SQL = "Select * from DomainsAndIPs Order By Service_Web_FQDN IIF(IsNull(Service_Web_FQDN), 'ZZZ', Service_Web_FQDN) ASC"

    And it produced this:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'Service_Web_FQDN IIF(IsNull(Service_Web_FQDN), 'ZZZ', Service_Web_FQDN)'.
    /sortable.asp, line 39

    Any ideas???

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Cool use isnull() in sort order

    Try:

    Order By isnull(Service_Name), Service_Name ASC

    isnull() returns 0 for false, -1 for true.

    blindman

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    ...more....

    .....Isnull() behaves differently in Access than in Microsoft SQL!

  4. #4
    Join Date
    Jun 2003
    Location
    New Jeruz
    Posts
    3

    no cigar

    Thanks blindman, however I got the same results as before.

    All the blank ones show up first AND THEN a's... b's...c's....d's...

    I used:
    SQL = "Select * from DomainsAndIPs Order By isnull(Service_Name) ASC".

    If there was a way to say Okay, you're blank, you go last... That would be it.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    oops!

    Sort the isnull in Descending order:

    Order By isnull(Service_Name) desc, Service_Name ASC

    Also, make sure your field doesn't actually contain zero-length strings.

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    New Jeruz
    Posts
    3

    First timer...

    It's my first time on this board, and I thank you so much blindman, it worked.
    I hope to contribute as much as I can here.

    Once again, thanks!

    white300z

Posting Permissions

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