Reports with multiple criteria 1

[hansretallick]

I need to open the same report from different forms. Either form selects a filter (TenancyNo) and the query opened by the report needs to make this field the criteria for the result to be displayed.
I have tried using an OR statement, which works OK, however, when opening the report, an error message appears referring to the form which is not being used to initiate the report. Upon cancelling the error, the report displays the correct records.
In the past I have overcome this problem by running two separate queries and two identical forms, which always seems to me to be wasteful and inefficient (especially if the form needs to be modified later).
Is there any way that I can use the same report and query for this?

sql is as follows:

SELECT tblHouse.Address, tblTenancy.TenancyNo, tblTenancyNotes.Time, tblTenancyNotes.Notes, tblTenancy.StartDate, tblTenancy.EndDate
FROM (tblHouse INNER JOIN tblTenancy ON tblHouse.[No] = tblTenancy.HouseNo) INNER JOIN tblTenancyNotes ON tblTenancy.TenancyNo = tblTenancyNotes.TenancyID
WHERE ([highlight #FCE94F]((tblTenancy.TenancyNo)=[Forms]![frmTenancyEdit]![TenancyNo])[/highlight] AND ((tblTenancy.EndDate) Is Null)) OR ([highlight #FCE94F]((tblTenancy.TenancyNo)=[Forms]![frmQuickFindTenancy]![TenancyNo])[/highlight] AND ((tblTenancy.EndDate) Is Null));

 

[dhookom]

I try to never use dynamic criteria like this in record source queries. Consider removing the criteria and using a WHERE CONDITION in the DoCmd.OpenReport method. This makes your report much more flexible.

The other alternative is to use a little DAO/vba code to change the SQL property of a saved query that is used as the record source.

Duane
Hook'D on Access
MS Access MVP