Grouping on certain years - help needed please

  • Thread starter Thread starter neil40
  • Start date Start date
N

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
 
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)
Click to expand...


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"
 
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"
Click to expand...

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
 
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
Click to expand...


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.
 
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
Click to expand...
Team XYZ
Pre-War results
1930
1931
Post-War results
1946
1947
Click to expand...

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 -
Click to expand...

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
 
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 -
Click to expand...

....just to add, the report uses
Group on Team
Sort by Season
 
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'
Click to expand...


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.
 
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
Click to expand...
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'
Click to expand...

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 -
Click to expand...

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
 
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.
Click to expand...
Now use the expression:
=IIf(Season < 1945,"Pre", "Post") & " War"
in a text box in the new group header section.
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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
 
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!)
Click to expand...


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"
 
Back
Top