Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Starts with A but appears last (Access2000-03)

    Another from me:-(

    I have
    Report Title
    Region
    Office

    all in Page Header.

    My Regions included "ALL Central", "ALL East" and "ALL West" values.
    I want those to be seen last. How can I do that?

    I have only 5 of those so I can hard code it.

    Thanks

    My

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

    Re: Starts with A but appears last (Access2000-03)

    Do you have a table listing the regions? If so, I'd add a field (Number, field size Integer) named SortOrder, and enter an appropriate value for each region (1 = first, 2 = second etc.).
    Include the SortOrder field in the Record Source of the report, and set the report to sort on SortOrder in the Sorting and Grouping window, instead of on Region itself.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    You could create a dummy field that changes you ALLs to ZZZZZALLs and then sort on that (hidden) dummy field.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    Exactly! Thanks for this solution!

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    Edited by HansV to replace Word doc with image it contained - easier for others

    OK I am affraid not is going to happen. And here is why.
    Attached is a table report based on.
    Where highlighted values are - it use to be empty.
    I had created table with values and UPDATED table with values that are highlighted.
    NOW
    I am running perfect Report with perfect data except those fields need to be not at the beginning.
    So how can I assign ZZZZZZZzs or anything to the small portion of data only? Because the rest is abc ok.

    Thanks so much
    Attached Images Attached Images

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

    Re: Starts with A but appears last (Access2000-03)

    I don't understand what you have done.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    The table originally looked like this image but all values in black are absent!
    I had created table with these values (I was given values) and appended then into the table based on LOC values 73.74.75-1003
    Now table looks like what you see in image.
    And ofcouse lucky me got all these values started with A and I need them at the end of the Report.

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

    Re: Starts with A but appears last (Access2000-03)

    If you can't use the suggestion from my earlier reply, try this:
    - Create a query based on the table.
    - Add the fields you need for the report to the query grid.
    - Create a calculated column in the query:
    <code>
    SortRegion: Replace([Region],"All ","ZZZ ")
    </code>
    - Use the query as Record Source for the report.
    - Specify that the report should be sorted on SortRegion in the Sorting and Grouping window.

    Note: Replace doesn't work in Access 2000, only in Access 2002 and later. If you need to support Access 2000, use the following instead:
    <code>
    SortRegion: IIf([Region] Like "All *","ZZZ " & Mid([Region],4),[Region])</code>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    Nono, I am 2000 converted into 2003. So I should be ok.
    Thanks Hans, I am so warned out by these little crazynesties!

    Thanks again!

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    Hans!
    It works great except I see all the ZZZs now, can I use some Trim function or Left...what is best?
    And What is some Region with Z will be added one day?

    Thanks so much!

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

    Re: Starts with A but appears last (Access2000-03)

    You should use SortRegion in the Sorting and Grouping window, but use Region itself in the report, so that you see the original value in the report.

    If you have regions whose real name starts with Z, they will be sorted just before the regions starting with All - after all, it's unlikely that there'll be a real name starting with 3 Zs.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    Hans!
    I trully am your biggest fan now!
    Thanks you so much and I am soooooooo very greatful to you for your help!

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

    Re: Starts with A but appears last (Access2000-03)

    So - would you like me to change your Lounge username? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Just kidding!

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starts with A but appears last (Access2000-03)

    It depends what name you have in storage for me! :-)

Posting Permissions

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