Grouping on certain years - help needed please

[neil40]

Hi
I have a database in Access 2007 that records details of sports clubs
and historical results.
Records start in 1929 to present date with a break for World War 2
I have reports showing League tables etc.

One report shows all the clubs history on one page with club details
at the top, then line by line history of results (Played/Won/Drew/For/
Against etc)

However, I would like to group these latter results in pre-war
(1929-1939) and post-war (1946- ) periods (all on the same report)

I'd like advice please as how best to do this

Many thanks
Neil

 

[Marshall Barton]

I have a database in Access 2007 that records details of sports clubs
and historical results.
Records start in 1929 to present date with a break for World War 2
I have reports showing League tables etc.

One report shows all the clubs history on one page with club details
at the top, then line by line history of results (Played/Won/Drew/For/
Against etc)

However, I would like to group these latter results in pre-war
(1929-1939) and post-war (1946- ) periods (all on the same report)

Assuming you have no records for 1940-1945, you can just set
the Sorting and Grouping expression to

=yearfield < 1945

In the group header section text box:

=IIf(yearfield < 1945,"Pre", "Post") & " War"

 

[neil40]

Assuming you have no records for 1940-1945, you can just set
the Sorting and Grouping expression to

=yearfield < 1945

In the group header section text box:

=IIf(yearfield < 1945,"Pre", "Post") & " War"

Thanks
Perhaps you can advise a little more.
I have managed to create 2 reports, one post and one pre war, but I
can't figure out how to have both on the same report so that I ge
tsomething like

Team XYZ
Pre-War results
1930
1931
Post-War results
1946
1947
.....

Thanks
Neil

 

[Marshall Barton]

Perhaps you can advise a little more.
I have managed to create 2 reports, one post and one pre war, but I
can't figure out how to have both on the same report so that I ge
tsomething like

Team XYZ
Pre-War results
1930
1931
Post-War results
1946
1947

But that's exctly what my previous post does. Did you try
it? If so, what about it didn't do what you want? If not,
why not.

 

[neil40]

Perhaps you can advise a little more.
I have managed to create 2 reports, one post and one pre war, but I
can't figure out how to have both on the same report so that I ge
tsomething like

Team XYZ
Pre-War results
1930
1931
Post-War results
1946
1947

But that's exctly what my previous post does. Did you try
it? If so, what about it didn't do what you want? If not,
why not.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

OK, I'll explain what I have and did in response to your original post
Marsh.
It's Access 2007 by the way.
My report has
Report Header (Contains some explanation text)
Page Header (Blank)
Team Header (Column Headings)
Detail (Obviously where the year by year records print)
Team Footer

Rather than 'yearfield' I have 'Season'
I put a text box in to the Team Header with the IIf statement that you
indicated (but modified to my table name)
Then in the Group, Sort and Total box at the bottom in design mode I
tried to change 'Sort by Season' to Sort by expression using the
(again modified) expression you gave.
I get the following error when I run the preview
'Data type mismatch in Expression'

I'm obviously not putting something in the correct place!
Neil

 

[neil40]

OK, I'll explain what I have and did in response to your original post
Marsh.
It's Access 2007 by the way.
My report has
Report Header (Contains some explanation text)
Page Header (Blank)
Team Header (Column Headings)
Detail (Obviously where the year by year records print)
Team Footer

Rather than 'yearfield' I have 'Season'
I put a text box in to the Team Header with the IIf statement that you
indicated (but modified to my table name)
Then in the Group, Sort and Total box at the bottom in design mode I
tried to change 'Sort by Season' to Sort by expression using the
(again modified) expression you gave.
I get the following error when I run the preview
'Data type mismatch in Expression'

I'm obviously not putting something in the correct place!
Neil- Hide quoted text -

- Show quoted text -

....just to add, the report uses
Group on Team
Sort by Season

 

[Marshall Barton]

OK, I'll explain what I have and did in response to your original post
Marsh.
It's Access 2007 by the way.
My report has
Report Header (Contains some explanation text)
Page Header (Blank)
Team Header (Column Headings)
Detail (Obviously where the year by year records print)
Team Footer

Rather than 'yearfield' I have 'Season'
I put a text box in to the Team Header with the IIf statement that you
indicated (but modified to my table name)
Then in the Group, Sort and Total box at the bottom in design mode I
tried to change 'Sort by Season' to Sort by expression using the
(again modified) expression you gave.
I get the following error when I run the preview
'Data type mismatch in Expression'

I guess I wasn't clear enpugh. You were supposed to add the
expression:
=Season < 1945
to Sorting and Grouping between Team and Season. Specify
that you want the group header for the new group.

Now use the expression:
=IIf(Season < 1945,"Pre", "Post") & " War"
in a text box in the new group header section.

 

[neil40]

OK, I'll explain what I have and did in response to your original post
Marsh.
It's Access 2007 by the way.
My report has
Report Header (Contains some explanation text)
Page Header (Blank)
Team Header (Column Headings)
Detail (Obviously where the year by year records print)
Team Footer

Rather than 'yearfield' I have 'Season'
I put a text box in to the Team Header with the IIf statement that you
indicated (but modified to my table name)
Then in the Group, Sort and Total box at the bottom in design mode I
tried to change 'Sort by Season' to Sort by expression using the
(again modified) expression you gave.
I get the following error when I run the preview
'Data type mismatch in Expression'

I guess I wasn't clear enpugh. You were supposed to add the
expression:
=Season < 1945
to Sorting and Grouping between Team and Season. Specify
that you want the group header for the new group.

Now use the expression:
=IIf(Season < 1945,"Pre", "Post") & " War"
in a text box in the new group header section.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

OK, I'd actually tried this.
I now have
Page Header
Team Header
=[Season]<1945 Header (Access added the [ ] as you'll know
Detail
Team Footer
etc

I still get a Data type mismatch in criteria expression error

My Group Section looks like
Group on Team
Group on expression =[Season]<1945
Sort on Season

The Text box has the control source =IIf([Season] < 1945,"Pre",
"Post") & " War"
Again access added the brackets

Neil

 

[neil40]

I guess I wasn't clear enpugh. You were supposed to add the
expression:
=Season < 1945
to Sorting and Grouping between Team and Season. Specify
that you want the group header for the new group.

Now use the expression:
=IIf(Season < 1945,"Pre", "Post") & " War"
in a text box in the new group header section.

- Show quoted text -

OK, I'd actually tried this.
I now have
Page Header
Team Header
=[Season]<1945 Header (Access added the [ ] as you'll know
Detail
Team Footer
etc

I still get a Data type mismatch in criteria expression error

My Group Section looks like
Group on Team
Group on expression =[Season]<1945
Sort on Season

The Text box has the control source =IIf([Season] < 1945,"Pre",
"Post") & " War"
Again access added the brackets

Neil- Hide quoted text -

- Show quoted text -

Marsh
I figured the problem.
The field in the table was a text field (Season), hence < 1945 won't
work!
I've now changed it to number and it works a treat

Thanks for your help (and patience!)

Regards
Neil

 

[Marshall Barton]

I figured the problem.
The field in the table was a text field (Season), hence < 1945 won't
work!
I've now changed it to number and it works a treat

Thanks for your help (and patience!)

Glad you figured it out and you're welcome.

BTW, you did not have to change the field data type. You
could have changed the expressions to do the conversion.
For example:

CLng(season) < 1945
or
season < "1945"