Reports, Querydefs and parameters

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

The situation:

I have created a query which is to be used as the basis
for an Access report. The query has 3 parameters
(StartDate, EndDate and Type) that limit the data returned
to one of two 'types' and a particular month
(using 'BETWEEN [StartDate] AND [EndDate]'). The date
range will always be the first and last day of a
particular month as the report will be used to produce a
monthly summary.

However, I would like to avoid the user having to type in
3 parameters. The [Type] parameter can easily be
determined by code. I have also written a small function
that uses a single input box to collect the month the user
wants and then calculates the StartDate and EndDate. A
Querydef object of the original query is then created and
the parameters applied accordingly.

The problem:
Somehow I need to be able to make the Querydef object
(along with its newly assigned parameters) the record
source of the report but I'm not sure how to do this. Any
attempt seems to result in the user still being prompted
for each parameter and I don't seem to be able to make the
actual Querydef object the record source.

Thank you in advance for any help!
 
Would you be able to use the WhereCondition of the OpenReport action? That
way you don't need the parameters in the query, and just code up the string
when you open the report. For an example of that, see Method 2 in this
article:
http://members.iinet.net.au/~allenbrowne/casu-08.html

If that is not suitable, you could build the entire SQL string in the
report's Open event, and assign it as the form's RecordSource:
Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
strSQL = "SELECT ...
Me.RecordSource = strSQL
End Sub

If that is still not suitable, it is possible to overwrite the SQL property
of the querydef with the SQL string before you open the report. This
approach is best for getting you out of a tight spot with subreports or
stacked queries:
strSQL = "SELECT ...
dbEngine(0)(0).QueryDefs("MyQuery").SQL = strSQL
 
Back
Top