Combobox to filter a y/n field

[moishy101]

What is the proper syntax to a use a combo box to filter results of a report based on a y/n field and apply no filter if the combo box is empty?

this is what I have so far:

strWhere = " YesNoField Like " & IIf([Forms]![frmReports]![cboYesNo] = "Yes", True, False)

But it won't account for an empty combo box.

[twinnyfo]

moishy101,

I would have the RecordSource for the Report be based on a query that uses the values from the Yes/No CheckBox and the ComboBox on the form. Very simple to do in the query design process.

You can establish query parameters that prevent filtering if the ComboBox is empty, also.

Let me know if this helps, or send additional info if required.

Cheers!

[moishy101]

I don't want to change the record source of the report, I'm trying to filter using the where clause of DoCmd.OpenRepor t.

[twinnyfo]

I'm not sure the record source can be changed in a report by itself, because it is technically a static set of data. It is possible to have controls on a form which calculate based on other controls, but I know of no way to change the record source. Perhaps there might be some experts who know how to do it. I would be interested to know if it's possible.....

I guess that is why my recommendation was to have a dynamic query that provided a recordset according to certain conditions and criteria. Then you have the best of both worlds. You never have to mess with the record source on the report, but get the necessary records based on criteria external to the report.

[moishy101]

@twinnyfo

It's very simple to change the record source of a report (Me.RecordSourc e = strSQL, in the on open event).

But as I mentioned that is not how I want it done.

[Rabbit]

Account for an empty combo box in what way?

[moishy101]

If the combo box is empty not to apply that filter (there are other filters).

[moishy101]

I tried this but it doesn't seem to filter properly.

    If Not IsNull(cboForSale) Then
        strWhere = strWhere & " And" & " A.ForSale Like " & IIf([Forms]![frmReports]![cboForSale] = "For Sale", True, False)
    End If

[moishy101]

On second thought maybe the problem is with the rest of the sub.

    Dim strWhere As String
    strWhere = " A.City Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'" _
               & " And" & _
               " A.Neighborhood Like '" & Nz([Forms]![frmReports]![cboNeighborhood], "*") & "'"

Would the use of Nz() above show all records (not apply the filter for that field) if the combo boxes are empty?

[twinnyfo]

moishy101,

I'm still trying to understand why it is so critical not to use a query based on the Form you have loaded? In terms of programming, it really is the easiest and best way to do it. Reports aren't really designed to be dynamic, but rather static. The data underneath them can be very dynamic, which is why we design queries that take into account external parameters, such as values in combo boxes on forms.

Is there are particular reason the report absolutely must retain its current record source? Because, based on what you are trying to do, you are changing the record source, without "changing the record source."

An alternate option for this, which, again is form-based and not report-based, is to open the report with its normal record source, then apply a filter using the VBA code from the form. This would do what you want to do, but not how you want to do it.

Additional thoughts?

[moishy101]

An alternate option for this, which, again is form-based and not report-based, is to open the report with its normal record source, then apply a filter using the VBA code from the form. This would do what you want to do, but now how you want to do it.

Take a look at the help files on the OpenReport method.

[twinnyfo]

Could you please post the code you have to develop your filter string and the code you use to open your report? Also, are you getting errors or just not the records you want? It would also help to know the recordset behind the form, as this will determine how your filter is going to limit data.

I think this would help me troubleshoot.

[moishy101]

twinnyfo

Thanks for your time, I think I got it sorted for now.