Complicating an already complicated report

[biggcc]

I have been asked to make changes to a database report that I had nothing to do with when it was created. Now normally that would not be a big deal but this particular database and report are different. We currently have a sales report that is pulling data from our SQL server. There is a macro which runs multiple (about 20) delete and append queries to populate the tables/queries. All of which is pretty standard stuff. Where I'm having a problem is with the report. Below is the record source select statement that is being used. I need to be able to select a single, multiple or all properties to be returned from this report. I created a dialog box which should work but I don't know how to incorporate it into this statement. The field that it should key on is tbl_Property_Tenant_Data.SADDR1. Maybe I'm just missing something so I thought I'd have some extra eyes take a look at this.

SELECT tbl_Property_Tenant_Data.HMY, tbl_Property_Tenant_Data.SCODE, tbl_Property_Tenant_Data.SADDR1, tbl_Property_Tenant_Data.ISTATUS, tbl_Property_Tenant_Data.HMYPERSON, tbl_Property_Tenant_Data.SLASTNAME, tbl_Property_Tenant_Data.SRENT, tbl_Property_Tenant_Data.DLEASEGROSSSQFT, tbl_Property_Tenant_Data.SUNITCODE, tbl_Property_Tenant_Data.DTNOTICE, tbl_Property_Tenant_Data.DTLEASETO, tbl_Property_Tenant_Data.SLEASEBUSTYPE, tbl_Property_Tenant_Data.SFIELDS13, tbl_Property_Tenant_Data.SFIELDS12, tbl_Property_Tenant_Data.ILEASEOVGMONTH, tbl_Overage_Months.[Year End], [tbl_Tenant_%Rent_Paid_CurrYear].SumOfCurrYearTotal FROM (tbl_Overage_Months RIGHT JOIN tbl_Property_Tenant_Data ON tbl_Overage_Months.[Ovg Mo] = tbl_Property_Tenant_Data.ILEASEOVGMONTH) LEFT JOIN [tbl_Tenant_%Rent_Paid_CurrYear] ON tbl_Property_Tenant_Data.HMYPERSON = [tbl_Tenant_%Rent_Paid_CurrYear].HMYPERSON GROUP BY tbl_Property_Tenant_Data.HMY, tbl_Property_Tenant_Data.SCODE, tbl_Property_Tenant_Data.SADDR1, tbl_Property_Tenant_Data.ISTATUS, tbl_Property_Tenant_Data.HMYPERSON, tbl_Property_Tenant_Data.SLASTNAME, tbl_Property_Tenant_Data.SRENT, tbl_Property_Tenant_Data.DLEASEGROSSSQFT, tbl_Property_Tenant_Data.SUNITCODE, tbl_Property_Tenant_Data.DTNOTICE, tbl_Property_Tenant_Data.DTLEASETO, tbl_Property_Tenant_Data.SLEASEBUSTYPE, tbl_Property_Tenant_Data.SFIELDS13, tbl_Property_Tenant_Data.SFIELDS12, tbl_Property_Tenant_Data.ILEASEOVGMONTH, tbl_Overage_Months.[Year End], [tbl_Tenant_%Rent_Paid_CurrYear].SumOfCurrYearTotal HAVING (((tbl_Property_Tenant_Data.ISTATUS)=0)) ORDER BY tbl_Property_Tenant_Data.SCODE, tbl_Property_Tenant_Data.SLASTNAME;

Thanks,
Chester Campbell

 

[biggcc]

Additional Information

I've tried adding the line below to the query in the report for the field SADDR1 but for some reason it doesn't like this statement. My filter screen comes up and allows me to make my selections but then I get another dialog box asking me for a SADDR1 value. Still working on it but thought this information might help.

Like IIf(IsNull([forms]![propdialog]![saddr1]),"*",[forms]![propdialog]![saddr1])

Thanks,
Chester Campbell