Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Sort (2000)

    I am trying to recreate a report that someone did with another software program. I have a County field and a State field. I created a calculation that goes - CtySt:IIF([State]<>"PA",[State],[County]) That gives me county names for all the PA state but if it is another state then give me the State abbreviaition. So I have a list that has county names and state abbreviations. My problem is in the report I am trying to recreate, they have all the counties first in alpabetical order and then the state abbreviations in alphabetical order. How would I accomplish this in Access. Is it possible?

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

    Re: Report Sort (2000)

    Create another calculated field:
    InPA: [State]="PA"
    This will be True = -1 if the state is Pennsylvania, False = 0 otherwise. Sort the report on InPA first, then on CtySt.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sort (2000)

    I made the calculated field as you said. Where do I put True =-1 if the state is Pennsylvania, False = 0 Otherwise. Do you mean an IIF statement - INPA:IIF([State]="PA",-1,0)

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

    Re: Report Sort (2000)

    What I meant was to put the following in an empty column of the query, literally:

    <code>InPA: [State]="PA"</code>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sort (2000)

    I put the calculated field as you said and it worked.

    Now I have another problem. In the county field there are some blanks so I have a calculation - ctyblank:IIF([county]= " ","Other",[county]. I used ctyblank in the calculation: CtySt: IIf([State]<>"PA",[State],[Ctyblank]) and I sorted on that but it comes back and says to enter parameter value for Ctyblank. I sorted on - INPA: [State]="PA" and CtyAbrev: IIf([State]<>"PA",[State],[Ctyblank]). When I use County instead of CtyBlank - it works. I have to define the blank fields as other.

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

    Re: Report Sort (2000)

    You MUST define the ctyblank column to the left of the CtySt column. If they are reversed, CtySt won't know what ctyblank is.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sort (2000)

    I just wanted to tell you how my sort worked out.
    I set up the first query with all the calculations and the INPA:[State] = "PA"
    I didn't sort this query. (For some reason it didn't work when I tried to sort the calculated fields)
    I did a second query, bringing in all the fields, sorted, grouped,summed etc and it worked perfectly.
    When I created the report, I brought in INPA and made it invisible.
    I went up to Sorting and Grouping and Sorted INPA first and then CtySt
    Works perfectly.
    Thanks for your help.

Posting Permissions

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