OK.
Well, you are right. You should have only one report and you should have only
one query for the report. What you need to do is remove ALL criteria from the
underlying query and then when you open the report pass the relevant where
clause to the report.
If you have six buttons you will need to code the six buttons to open the
report.
Private Sub btnAllRecords_Click()
Dim strWhere as String
strWhere = ""
docmd.OpenReport "strReportName",acViewPreview,,strWhere
End Sub
Private Sub btnByYear_Click()
Dim strWhere as String
strWhere = "EXPR1=""" & ME.[year1] & """"
docmd.OpenReport "strReportName",acViewPreview,,strWhere
End Sub
etc.
Better would be to have one button to open the report and an option group with
six options to specify which option you wish to use.
Private sub btnReport_Click()
Dim strWhere as String
Select Case Me.OptionGroupName
Case 1 'All records
strWhere = ""
Case 2 'All Sports by date range
strWhere = "eventstart<=#" & Format(Me.[startdate],"yyyy-mm-dd") & _
"# AND eventend>=#" & Format(Me.[enddate],"yyyy-mm-dd") & "#"
Case 3 'All events by sport type
strWhere = "sporttype=""" & Me.[type1] & """"
Case 4 'All events for a specific year
strWhere = "EXPR1=""" & ME.[year1] & """"
'This one may need to read
'strWhere = "Year(EventStart)=" & Me.Year1
Case 5 'Sport Type and Year
strWhere = "sporttype=""" & Me.[type1] & """ AND " & _
"EXPR1=""" & ME.[year1] & """"
Case 6 'sport type and date range
strWhere = "eventstart<=#" & Format(Me.[startdate],"yyyy-mm-dd") & _
"# AND eventend>=#" & Format(Me.[enddate],"yyyy-mm-dd") & "#" & _
" AND "sporttype=""" & Me.[type1] & """"
End Select
DoCmd.OpenReport "strReportName",acViewPreview,,strWhere
end sub
You should probably add code to check that there are valid values in the
controls when needed and then handle the problem if there are problems.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
James said:
John,
Thanks your quick response. I did wonder if I was correctly explaining
myself, which is in part not helped by me not fully understanding the subject.
The first thing I need to establish is I've inherited this database when I
joined this company, and had in order to meet all the different requirements
over the years has been added to and tinkered with so that it's a massive
unwieldy beast.
So, for reporting on events you enter details into a form and then click on
one of 6 command buttons to open the corresponding report, of which there are
6. My problem is that firstly it looks horrid and is a pain to navigate, but
more importantly if you change the format or add a field you then have to go
through and alter 6 reports!
In my effort to stream line things and make it easier for future
developments is to have one report that can cope with 6 different arguments,
which are:
1) I want to see all records between two dates - e.g all events between
07/04/08 and 30/04/08
WHERE (((tbl_event.eventstart)<=[forms].[reportmenu].[startdate]) AND
((tbl_event.eventend)>=[forms].[reportmenu].[enddate]))
2) I want to see all records for a specfic sport type - e.g. all rugby union
events
OR (((tbl_event.sporttype)=[forms].[reportmenu].[type1]))
3) I want to see all records for a specific year - e.g. all events in 2009
(I've been using the datepart function as no-one included a year column in
the events table)
OR (((DatePart("yyyy",[eventstart],7,2))=[forms].[reportmenu].[year1]))
4) I want to see all records for a specific sport type in a specific year
(e.g. all rugby union events in 2009)
OR (((tbl_event.sporttype)=[forms].[reportmenu].[type1]) AND
((DatePart("yyyy",[eventstart],7,2))=[forms].[reportmenu].[year1]))
5) I want to see all records for a specific sport type between two dates -
e.g. all rugby union events between 07/04/08 and 30/04/08
OR (((tbl_event.eventstart)<=[forms].[reportmenu].[startdate]) AND
((tbl_event.eventend)>=[forms].[reportmenu].[enddate])) AND
((tbl_event.sporttype)=[forms].[reportmenu].[type1]))
6) I want to see all records - e.g. every single events that we've done or
have got planned
I managed to get 1,2,3 + 6 to work but it was the 4+5 that wouldn't play fair.
regards
JAMES
John Spencer said:
Its all a matter of parentheses (or placement of arguments if you are using
the design view). I can't make sense out of your criteria as posted.
Can you explain in words what you are trying to accomplish?
For instance what you might want is the following which should return all
records of the specified type for the specified year
WHERE (tbl_event.sporttype=[forms]![reportmenu]![type1]
AND DatePart("yyyy",[eventstart],7,2)=[forms]![reportmenu]![year1])
Also, I can't see any reason for using DatePart instead of Year.
WHERE (tbl_event.sporttype=[forms]![reportmenu]![type1]
AND Year([eventstart])=[forms]![reportmenu]![year1])
So can you post an explanation like
I want to see all records with the specified type and specified year plus all
records within the specified date range regardless of sport.
Or
I want to see all records with the specified type and specified year plus all
records within the specified date range for the specified type.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
James Frater wrote:
Hello All,
I've been tearing my hair out over this, so any help on this would be
greatly appreciated and, I'd also start to look less like Opi from Family
Guy!!!!!!
The first three arguments work perfectly, however where I get stuck is
combining arguments.
So for the last argument I want to return values for sport type in a year.
For example I want to list all my Rugby Union events in 2009, but the bloody
thing returns every Rugby Union event for all years and all events in 2009.
Any ideas chaps?
JAMES
SELECT tbl_event.eventstart, tbl_event.eventend, tbl_event.eventname,
tbl_event.location, tbl_event.country, tbl_event.sporttype,
DatePart("yyyy",[eventstart],7,2) AS Expr1
FROM tbl_event
WHERE (((tbl_event.eventstart)<=[forms].[reportmenu].[startdate]) AND
((tbl_event.eventend)>=[forms].[reportmenu].[enddate]))
OR (((tbl_event.sporttype)=[forms].[reportmenu].[type1]))
OR (((DatePart("yyyy",[eventstart],7,2))=[forms].[reportmenu].[year1]))
OR (((tbl_event.sporttype)=[forms].[reportmenu].[type1]) AND
((DatePart("yyyy",[eventstart],7,2))=[forms].[reportmenu].[year1]))
ORDER BY tbl_event.eventstart;
