By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Combobox to filter a y/n field

P: 46
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:
Expand|Select|Wrap|Line Numbers
  1. strWhere = " YesNoField Like " & IIf([Forms]![frmReports]![cboYesNo] = "Yes", True, False)
But it won't account for an empty combo box.
Jul 26 '12 #1
Share this Question
Share on Google+
12 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,032
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!
Jul 26 '12 #2

P: 46
I don't want to change the record source of the report, I'm trying to filter using the where clause of DoCmd.OpenReport.
Jul 26 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,032
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.
Jul 26 '12 #4

P: 46
@twinnyfo

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

But as I mentioned that is not how I want it done.
Jul 26 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
Account for an empty combo box in what way?
Jul 26 '12 #6

P: 46
If the combo box is empty not to apply that filter (there are other filters).
Jul 27 '12 #7

P: 46
I tried this but it doesn't seem to filter properly.
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(cboForSale) Then
  2.         strWhere = strWhere & " And" & " A.ForSale Like " & IIf([Forms]![frmReports]![cboForSale] = "For Sale", True, False)
  3.     End If
  4.  
Jul 27 '12 #8

P: 46
On second thought maybe the problem is with the rest of the sub.
Expand|Select|Wrap|Line Numbers
  1.     Dim strWhere As String
  2.     strWhere = " A.City Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'" _
  3.                & " And" & _
  4.                " A.Neighborhood Like '" & Nz([Forms]![frmReports]![cboNeighborhood], "*") & "'"
  5.  
Would the use of Nz() above show all records (not apply the filter for that field) if the combo boxes are empty?
Jul 27 '12 #9

twinnyfo
Expert Mod 2.5K+
P: 3,032
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?
Jul 27 '12 #10

P: 46
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.
Jul 27 '12 #11

twinnyfo
Expert Mod 2.5K+
P: 3,032
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.
Jul 27 '12 #12

P: 46
twinnyfo

Thanks for your time, I think I got it sorted for now.
Jul 27 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.