Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

sorting addresses

Discussion in 'Information Technology' started by sternface, Jul 28, 2006.

  1. sternface

    sternface
    Expand Collapse
    Guest

    I am trying to sort a field that is 345 south main street, when I sort the
    column of address they appear lowest numeric number first.
    How do I convert 345 south main street and so on that is in one field to 2
    fields: 345; south main street, so that the sort works properly?
     
  2. Loading...

    Similar Threads Forum Date
    Canada Sikh Community Addresses Civil & Human Rights Issues (Global Sikh Conference) Breaking News Dec 25, 2010
    Christianity Fourth Commandment addresses obligations owed to parents, country (Portsmouth Herald) Interfaith Dialogues Jul 2, 2005

  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Use two calculated fields in the query as the sort fields. Something like
    this:

    SELECT * FROM Tablename
    ORDER BY Trim(Left([AddressField], InStr([AddressField, " ")-1)),
    Trim(Mid([AddressField], InStr([AddressField, " ")+1));

    --

    Ken Snell
    <MS ACCESS MVP>


    "sternface" <sternface@discussions.microsoft.com> wrote in message
    news:C252BA22-B1F6-4003-BCEC-642F8751407C@microsoft.com...
    >I am trying to sort a field that is 345 south main street, when I sort the
    > column of address they appear lowest numeric number first.
    > How do I convert 345 south main street and so on that is in one field to 2
    > fields: 345; south main street, so that the sort works properly?
     
  4. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    To add to my post, you might have to cast the first part of the field as a
    number to get numeric order sorting:

    SELECT * FROM Tablename
    ORDER BY CLng(Trim(Left([AddressField], InStr([AddressField, " ")-1))),
    Trim(Mid([AddressField], InStr([AddressField, " ")+1));

    --

    Ken Snell
    <MS ACCESS MVP>


    "sternface" <sternface@discussions.microsoft.com> wrote in message
    news:C252BA22-B1F6-4003-BCEC-642F8751407C@microsoft.com...
    >I am trying to sort a field that is 345 south main street, when I sort the
    > column of address they appear lowest numeric number first.
    > How do I convert 345 south main street and so on that is in one field to 2
    > fields: 345; south main street, so that the sort works properly?
     

Share This Page