Grouping Problem

[Bob Barnes]

Access 2003...

Simple Query that includes "YrGrad" (Year Graduated).
It's a text field because the User wants to be able to have
"2006", "2005, 2006", "UK" (the School...working to get them
to change that to year/ years).

I use a DoCmd.OpenReport...to filter whatever is selected in a dropdown.
If "All Years" is selected in the Dropdown (part of a Union Select), there
is no filter in the DoCmd.OpenReport. When "All Years" (all records in the
Query) opens, the Grouping goes crazy...IE..."UK", "93, 95", "2006" and
"1987" is all that Groups.

Ideas?

TIA - Bob

 

[Duane Hookom]

What are the actual values? What do you mean by "goes crazy"? Report sorting
and grouping almost always works the way it is programmed to work.

It sounds like you have a data issue that should be cleaned up. You can
either modify your data or create a small function that uses the stored value
to convert to something that has more integrity.

 

[Bob Barnes]

Duane - Someone else wrote the database.

"YrGrad" is a Text Field...rather than restricting them to 1999, 2000, 2001,
....2007 etc. So, if they want to enter "YrGrad" as "2006, 2007" the User has
wanted that before.

I could convert everything to numbers, and restrict to ONE year...but
Grouping should work on individual strings (text)...right?

This is the first time I've seen Grouping "fail"...

In the Report, I just do "Sorting and Grouping" by "YrGrad"...Descending.

Thank you - Bob

 

[Duane Hookom]

Why do you think this is failing? What are some values that aren't sorted as
you would expect? How do they appear in the report?

 

[Bob Barnes]

Duane...Trying to guess what's causing it to fail...

There are entries going back to "1940" thru "2006"...a "UK", a "93, 95, 97",
some others like "1992, 1993". "YrGrad" is a text field.

When the Query is NOT filtered, it sorts like "UK", "93, 95, 97", "2006",
and "1987"....NONE of the other strings are Grouped. I am not at the Client,
but I have a "Running Sum - Over All" in the Detail... (because the Report is
a Copy of a Report format that was not Grouped.). Wondering if somehow the
"Over All" affects it? I can test to "Over Group" when I'm there again. Of
course the "Running Sum...Over All" starts w/ "1" thru "1084" for the
displayed Groupings of...
"UK", "93, 95, 97", "2006", and "1987"

Does that help? I've Programmed Access starting in 1995, and have never
seen this before.

Thank you - Bob

 

[Duane Hookom]

You stated "the Query is NOT filtered, it sorts like "UK", "93, 95, 97",
"2006", ..." I'm am not convinced that you are using the Sorting and
Grouping dialog in the report design view. If you did, I would expect the
YrGrad field to sort like:

1940
.....
1992
1993
.....
2006
2007
....
93
94
95
.....
UK

What order is your report actually displaying these values? Is this field
the only sorting and grouping level?

 

[Bob Barnes]

Duane...

What order is your report actually displaying these values? Is this field
the only sorting and grouping level?

This Field is NOT the only one sorting, but is the only Grouping field.

I have in "Sorting and Grouping" this order

YrGrad - Descending
LastName - Ascending
FirstName - Ascending

The LastName & FirstName are "sorts" only...in Detail only.

Thank you for hanging in there on this. If "worse comes to worse", I'll
play w/ this more when I return to the Client. "If" this could affect this
(which I doubt) it's a Peer-to-Peer Server w/ 4 Users.

Bob

 

[Duane Hookom]

I was hoping you would retype or organize the values that I provided in the
order that your report is displaying them.

 

[Bob Barnes]

Sorting Ascending on the Grouping of "YrGrad" would give the Sort Order you
listed.

I had Sorting Descending on the Grouping of "YrGrad"...and it gave 4
groups...in this order...When there again, I can try Sorting Ascending on the
Grouping of "YrGrad".

This is what I had.....

UK
.....
93, 95, 97
.....
2006
.....
1987
.....

....Even though there were many other Groupings of 1940,.....,2005

Does this help?

TIA - Bob