Query from 2 combo boxes

[frons]

Hello I am trying to build a query that gets criteria from two combo boxes (cmb1 and cmb1) in a form (frm1) to build and populate a report.
In detail I need to set the query to work in the event that:

• the user makes a selection from only one of the combos
• the user makes selections from both combos.

It actually works if I check by running the query, but if I run the form that contains the two combo boxes, the self-generated report is filled with ALL the records from the table, unfiltered!

This is what I put in the query (omitted insignificant details):

Field1: IIf([Forms]![frm1]![cbo1]<>"*";[tbl1.val1]=[Forms]![frm1]![cbo1];True)
Field2: IIf([Forms]![frm1]![cbo2]<>"*";[tbl1.val2]=[Forms]![frm1]![cbo2];True)
Criteria (both): <>False

What am I missing?

[June7]

Try:

WHERE fieldname1 LIKE [Forms]![frm1]![cbo1] & "*" AND fieldname2 LIKE [Forms]![frm1]![cbo2] & "*"

[frons]

Still populating the forum with all the records if I run it through the form (although the query itself perfectly works).
I'm sure it has something to do with leaving one of the two comboboxes unchecked (i.e. if I would like to select only one of the two options), but don't know how and why

[June7]

Your OP says you want to populate a report. So is this query with filter criteria referencing comboboxes the report RecordSource?

[frons]

Correct, the report has the query as record source.

[June7]

You said the query works. So the report is properly filtered?

If you want form to also be filtered, try code to requery the form. Or click on the Refresh button on the ribbon.

I don't use dynamic parameterized queries. I prefer code to build filter criteria. Review http://allenbrowne.com/ser-62.html

[frons]

You said the query works. So the report is properly filtered?

No, that's exactly the issue: if I run the query from its ribbon, it perfectly works, but if I run the form, the report it opens it's unfiltered at all.

If you want form to also be filtered, try code to requery the form. Or click on the Refresh button on the ribbon.

I think you meant Report, not Form. I'm ultimately interested in the Report to be properly filtered when opened from the Form.
I also tried to run the Report by opening it by itself and not from the Form: it (of course) asks me for the parameters and once I gave them it perfectly works, but not if I open it through the Form

[June7]

No, I meant what I said. You stated in post 3 you 'run it through the form' which indicated to me you wanted to apply filter to form as well as report.

By 'from its ribbon' do you mean open query object from Navigation Pane?

Not sure what you mean by 'open it through the form'. If the query is the report RecordSource then report should be filtered same as opening the query object. Wouldn't matter if the report is opened manually from Navigation Pane (as long as form is open) or by code behind a button on form.

At this point, you will have to provide the database for review if still need help.

[frons]

By 'from its ribbon' do you mean open query object from Navigation Pane?

Yes

Not sure what you mean by 'open it through the form'.

The Report is opened through the Form via a onClick event on a send button below the two comboboxes.

[isladogs]

You seem to be making this more complicated than it needs to be
There are several ways of doing this

See attached for a quick example based on a list of students for a fictitious school
As in your case, the report is based on a query which can be filtered by one or both combo boxes.
Note the use of wildcards in the filters
The report includes a label indicating which filters were used if any

The query used for the report also allows unfiltered output where neither combo is completed
If you don't want that, the easiest way is to have the button disabled until one or other combo is updated

There are other ways of doing the same thing including Me.FilterOn=True/False, Me.Filter = ....
In a real world example, I would use VBA rather than a query but the principle is the same.
I would also add buttons to clear the combo selection

However I'll leave you to adapt for your own purposes

HTH

[frons]

Thank you Colin, now it works in every case (one selection, both selections, no selection at all) and the Report is correctly populated.

Only issue is I would like to have the Header stating what kind of filter is on at the moment; I did like you suggested with a label, but it never shows anything.
The two parameter that can be filtered are both Short Text type.

[isladogs]

I thought you'd want that feature...
Make sure you
1. Assign a string variable to each item and define both as public variables in a module
2. Set the values in the combo after update event code
3. Use the report header format event for the caption
4. Set out the caption as I did for strSurname for both fields e.g.

"Surname Like " & strSurname & "; Forename Like " & strForename & ""

or if you want to enclose in single quotes use

"Surname Like '" & strSurname & "'" & "; Forename Like '" & strForename & "'"

[frons]

I have followed all the steps, adapting to my datas and needs, but nothing is shown.
This is the Sub ReportHeader_Format:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)    
            If Nz(strTema, "") <> "" And Nz(strTurno, "") <> "" Then
              Me.lblInfo.Caption = "Tema " & strTema & "; Turno " & strTurno
           ElseIf Nz(strTema, "") <> "" Then
             Me.lblInfo.Caption = "Theme " & strTema & ""
          ElseIf Nz(strTurno, "") <> "" Then
             Me.lblInfo.Caption = "Turn " & strTurno & ""
          Else
             Me.lblInfo.Caption = "Everybody"
         End If
        End Sub

I can't understand the mistake

[isladogs]

You didn't modify the first option

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)    
            If Nz(strTema, "") <> "" And Nz(strTurno, "") <> "" Then
              Me.lblInfo.Caption = "Tema " & strTema & "; Turno " & strTurno & ""
           ElseIf Nz(strTema, "") <> "" Then
             Me.lblInfo.Caption = "Theme " & strTema & ""
          ElseIf Nz(strTurno, "") <> "" Then
             Me.lblInfo.Caption = "Turn " & strTurno & ""
          Else
             Me.lblInfo.Caption = "Everybody"
         End If
        End Sub

If you are saying none of these work, then you must have forgotten something else as well e.g.
1. Have you added a label called lblInfo to the report header and made it visible
2. Did you set the values of strTema and strTurno in the combo after update events

[frons]

You didn't modify the first option

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)    
            If Nz(strTema, "") <> "" And Nz(strTurno, "") <> "" Then
              Me.lblInfo.Caption = "Tema " & strTema & "; Turno " & strTurno & ""

I missed the part in red, but nothing changed in the end

If you are saying none of these work, then you must have forgotten something else as well e.g.
1. Have you added a label called lblInfo to the report header and made it visible

Yes

2. Did you set the values of strTema and strTurno in the combo after update events

Yes

Private Sub cboTurno_AfterUpdate()    
  strTurno = Nz(Me.cmbTurno, "")
End Sub

Private Sub cboTema_AfterUpdate()
  strTema = Nz(Me.cmbTema, "")
End Sub

Private Sub btnInvia_Click()
   DoCmd.OpenReport "Rpt Studenti", acViewPreview
End Sub