1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

#### Posting Permissions

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