Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 format)

    The field is text data type. It stores a street number. I can't make it a numeric field because the street number could be something such as 5D or 12C rather than 982 or 13.

    Using Blue Street as an example, there are 2 addresses on Blue Street by which I want to sort.
    9 Blue Street
    41 Blue Street

    In a report these end up sorted as
    41 Blue Street
    9 Blue Street

    It's because Access sorts by the left-most number.

    How do I fix this so that the addresses will sort correctly?

    (Of course, I can't use Descending sort order, because that throws other addresses out of proper sort order - e.g. 12 Jane St. and 20 Jane St.)

    Thanks.

    Tom

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If there is always a number in the first position you can create a formula that will pad the entry with zeros then sort on that field.

    SortColumn: Right("00000" & Left([address],InStr([address]," ")-1),5) & Mid([address],InStr([address]," "),99)

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works perfectly!
    I am using SortColumn: Right("00000" & Left([FullAddress],InStr([FullAddress]," ")-1),5) & Mid([FullAddress],InStr([FullAddress]," "),99)

    Here's an anomaly.

    The report sort order is...
    Street (prefix characters 6, ASC)
    FullAddress (each value) -- This is what you called address

    All Streets and addresses work out correctly except for the following...
    5434 Wellington Rd. 39
    7748 Wellington Rd. 22
    then 2 addresses on Wellington Rd. 29

    Obviously, these are all separate street addresses.

    If I increase the # of prefix characters, I get too many separate streets.
    If I, instead of prefix characters, I group on Each Value, I get too many separate streets in cases where some entries are Ave. and other entries are Avenue, or St. and Street, etc.

    Other than going in the program and entering code to make sure that all avenues are entered as Ave. and all streets are entered as St., etc. is there a way to fix this?

    Tom

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All Streets and addresses work out correctly except for the following...
    5434 Wellington Rd. 39
    7748 Wellington Rd. 22
    then 2 addresses on Wellington Rd. 29
    How should they sort?

    If I increase the # of prefix characters, I get too many separate streets.
    Not sure what you mean by too many sheets. The formula should be a hidden field and can be placed anywhere on the canvas (in the appropriate section of course).

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Re...
    5434 Wellington Rd. 39
    7748 Wellington Rd. 22
    then 2 addresses on Wellington Rd. 29

    The issue isn't an incorrect sorting. The issue is that Wellington Rd. 39, Wellington Rd. 22 and Wellington Rd. 29 are all separate and distinct streets

    There is a Street footer section, that forces a new page after the Street section. So each of these should show on a separate page.

    re...
    "not sure what you mean by too many sheets"
    I didn't use the word "sheets" but I assume you meant to type "streets."
    What I meant by changing the # of prefix characters results in too many streets is that, with the forcing of a new page after the Street footer section, Oxford Street and Oxford St. are seen by the program as two separate and distinct streets where, in fact, they are the same.

    Does that clarify?
    Tom

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oiy. If you sit back from your computer and squint your eyes the tr in streets looks like an h. I really need to get my glasses checked

    As far as the Wellington Rd. problem...
    You could split out the house number and the rest of the address into two different fields. Apply the padding to the house number field. Then sort on the street name and then the house number.

    As far as the Ave vs. Avenue vs. Av. ...
    That would probably have to be a manual process as far as changing them to a consistent format.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The HouseNbr and Street are 2 separate fields to begin with.

    I'll have to figure out how to pad the HouseNbr. in the query, in order that the sorting comes out properly in the report. Without the padding the HouseNbr itself doesn't sort properly in the report.

    I figured I'd have to work out a process whereupon there is consistency of entry with Avenues and Streets, Roads., etc.

    (my glasses get out of whack too often for my liking...and I hate to blame it on my eyes themselves!)

    Tom

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the query I plugged in this column
    SortHouseNbr: Format([HouseNbr],"0000")

    That does the padding, but it makes no difference in the situation
    5434 Wellington Rd. 39
    7748 Wellington Rd. 22
    then 2 addresses on Wellington Rd. 29

    They still come out as the same street, where they are separate streets.

    Tom

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I also tried this padding...
    Right("000" & [HouseNbr],10)

    Same result.

    Tom

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Without seeing your actual set up I can only make guesses. Can you post a sanitized version of your database with enough data to demonstrate the problem.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    mbarron
    Thanks for the offer of uploading a scaled down database, but it's not the easiest thing to do.

    Actually, since it's only this one page of streets that prints out this way, I am deciding to leave it alone. Just not worth the trouble in going further, and I don't want to make a career out of one report, so we will live with what I have.

    Thanks a lot for all of your help. It is very much appreciated.

    Tom

  12. #12
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I understand the problem. From your description, you are not using the FullAddress field (e.g. "Wellington Rd. 39") for the page break, but the field Street which is equal to the first 6 characters of the FullAddress field (in my example "Wellin"). This is a clever way to group on the same page records which most probably belong to the same street but where the address is not properly normalized: "Oxford Street" and "Oxford St." have the same first six characters ("Oxford") and will end up on the same page.

    I guess in your case there is no simple solution. Address normalization is quite a complex matter in databases. I have dealt in the past with a census database and we had a very sophisticated algorithm to standardize the address and to store each information in separate fields: the house number (which can contain letters or text like "bis" and "ter"!), the street type ("Street", "Avenue", ...) the street name ("Oxford", "Wellington", ...). And you will have to deal with ambiguity: an avenue called Saint Paul may be written "St. Paul Avenue" (the street of Mr. Paul Avenue?) or "Saint Paul Av.", so blindly replacing "St." by "Street" may give funny results...
    This eco-post is made of recycled electrons

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stephane
    You have captured the picture correctly. And there is no simple solution. But since these particular collection of streets (Wellington Rd. 22, 29 and 39) is the only anomaly, I'm going to let them appear on the same print-out page.

    Tom

Posting Permissions

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