Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following query that is trying to properly sort address name and text address number in proper ascending sequence.

    qry_Example_Sort_Number_Address_2

    <pre>SELECT tblBPermit.strAddrName, tblBPermit.strAddrNo,
    IIf(IsNull([strAddrNo]),0,IsNumeric([strAddrNo])) AS Expr1,
    IIf(IsNull([strAddrNo]),0,Val([strAddrNo])) AS Expr2
    FROM tblBPermit
    ORDER BY tblBPermit.strAddrName,
    IIf(IsNull([strAddrNo]),0,IsNumeric([strAddrNo])),
    IIf(IsNull([strAddrNo]),0,Val([strAddrNo]));
    </pre>



    Most of the streets are in sequence except for Transit Rd record 1416, 1417 & 1418

    <pre>Address Name Addr No Expr1 Expr2
    TRANSIT RD 6733 -1 6733
    TRANSIT RD 6733 -1 6733
    TRANSIT RD 6733 -1 6733
    Transit Rd 0 0
    Transit Rd 0 0
    Transit Rd 0 0
    Transit Rd 4721-4723 0 4721
    Transit Rd 4725-4727 0 4725
    Transit Rd 4725-4727 0 4725
    </pre>


    Is there anything I can do to fix this?

    Thanks, John

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

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    If you want addresses without a number to be sorted before addresses with a number, change

    IIf(IsNull([strAddrNo]),0,IsNumeric([strAddrNo]))

    to

    IIf(IsNull([strAddrNo]),True,IsNumeric([strAddrNo]))

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    I'm not sure I understand your problem. You say that 1416, 1417, & 1418 are out of sequence, but which ones are they? The query seems to be doing exactly what you want it to do; that is, putting all the valid numeric strAddrNo values before the non-numeric ones for a given street.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    Perfect!

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    I

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

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    You hadn't defined the calculated fields to sort on in the second part of the union query. And you must specify the ORDER BY clause only once at the end of the SQL, after all SELECT parts. You should not include ORDER BY clauses for the individual parts.
    See attached version.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Sorting Text Street and Number? (a2k (9.0.6926) SP-3 Jet 4.0 S

    Thanks

Posting Permissions

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