I searched the other threads under 1

[osoccers]

I searched the other threads under "Microsoft: Access Reports Forum" but couldn't find one on on the following issue:

A report called "rptDetailees" contains a group header called "Cost_Center Header" that is based on a text field called "Cost_Center," however some of the groups in the viewed report are out of order. The "Cost_Center" field takes four-character string values that begin with the leter Z, such as Z310, Z311, ..., Z393.

Unexplicably, the resulting goups in "rptDetailees" are slightly out of order as follows (I marked the out of sequence groups with an asterisk):

Cost_Center Page Number
----------- -----------
Z310 1
Z330 1
Z331 2
Z341 3, 4, 5
Z343 5
Z351 6
Z352 7
Z353 7
*Z313 7
*Z314 8
*Z344 8

( I added a column indicating the page of the report on which the group is printed.)

Thinking that the group would sort correctly if I pulled out the numerical part of the "Cost_Center" field, I changed the group heading from "Cost_Center Header" to "
=Val(Right([Cost_Center],3)) Header". This made no difference in the results, however.

Any ideas on how to obtain the correct group sort order?

 

[dhookom]

What are the actual values/expressions in your report's Sorting and Grouping dialog?

Duane
MS Access MVP

 

[osoccers]

Duane,

Here is a listing of all the information that you wanted from the grouping and sorting dialog window. Its best viewed in courier font so that the columns line up. Thanks for your interest.

-----------------------------------------
Sorting and Grouping Dialog Window
-----------------------------------------

Grp Field/Expression Sort Order
--- ---------------- ----------
Yes Cost_Center Ascending
Yes Program Ascending
No [LastName]&[FirstName] Ascending
No Auth_Date Ascending
-----------------------------------------

-------------------------------
Group Properties
-------------------------------

Cost_Center
-------------------------------
- Group Header = Yes
- Group Footer = Yes
- Group On = Each Value
- Group Interval = 1
- Keep Together = Whole Group
-------------------------------

Program
-------------------------------
- Group Header = Yes
- Group Footer = Yes
- Group On = Each Value
- Group Interval = 1
- Keep Together = Whole Group
-------------------------------

[LastName]&[FirstName]
-------------------------------
- Group Header = No
- Group Footer = No
- Group On = Each Value
- Group Interval = 1
- Keep Together = No
-------------------------------

Auth_Date
-------------------------------
- Group Header = No
- Group Footer = No
- Group On = Each Value
- Group Interval = 1
- Keep Together = No
-------------------------------

 

[dhookom]

Is Cost_center a lookup field in this table?

Duane
MS Access MVP

 

[osoccers]

Yes, Cost_Center is a simple lookup table that contains two fields as follows:

Cost_CenterID Cost_Center
1 Z310
2 Z311
21 Z312
22 Z313
23 Z314
6 Z330
7 Z331
8 Z332
25 Z333
9 Z340
10 Z341
11 Z342
12 Z343
24 Z344
13 Z350
14 Z351
15 Z352
16 Z353
26 Z393

(Sorry for my delayed response.)

 

[dhookom]

This is one of the many reasons that I detest lookup fields defined in tables. Your report is correctly sorted by the value contained your your field. Your table is storing the Cost_CenterID (which it should) but you see and think it is storing the Cost_Center.

You will need to add the Cost_Center lookup table to your report's record source so that you can sort by the field that actually contains Z310,...

http://www.mvps.org/access/lookupfields.htm

Duane
MS Access MVP

 

[osoccers]

Duane,
Thanks for your expert help troubleshooting this problem. I read the linked article regarding lookup fields. Since I neither designed the subject database nor devleoped its forms and reports, I'm not at fault for the existance of its lookup tables; however, I will learn from this situation to avoid them in my own work. Meanwhile, I have to figure out how to get to the report's record source so that I can modify it correctly.

Again, thanks.

 

[dhookom]

Can't you just add the lookup table to the report's record source? Add the real field to the grid so that you can use it to sort/group in your report.

Duane
MS Access MVP

 

[comaboy]

Just to throw in my 2p, take a look a at the Cost_CenterID field.

Cost_CenterID Cost_Center
1 Z310
2 Z311
21 Z312
22 Z313
23 Z314

It looks like it's a text format rather than a number. This may be contributing to your problem!