Choose all options in a Combo Box

[brendoaccess]

Hi

I have a form that allows the user to make choices of 3 collectors, Kim, George and David via a Combo Box, cboCollector, that feeds into a query and thence a report.

But if they don't know the parameter they want, or havent a preference and want to see a report that includes all the collectors, I'm stuck, because leaving the field blank returns no records - I thought about an Iif function in the criteria section of the Query for the field Collector:

IIf([Forms]![frmRunPremisesqry]![cboCollector] Is Null,""Kim" Or "George" Or "David"",[Forms]![frmRunPremisesqry]![cboCollector])

but it doesnt work. Woe is me.

All help appreciated. B

 

[Remou]

How about:

=[Forms]![frmRunPremisesqry]![cboCollector] & "*

 

[brendoaccess]

No good - returns no records, same as if left blank on the input form.

I thought of this;

IIf([Forms]![frmRunPremisesqry]![cboPremisesType] Is Null,"*",[Forms]![frmRunPremisesqry]![cboPremisesType])

but that's no good either.

 

[Remou]

[red]Like[/red] [Forms]![frmRunPremisesqry]![cboCollector] & "*"

 

[dhookom]

I would remove the criteria from the query entirely and use the Where Condition of the DoCmd.OpenReport method. Your code might look like:

Dim stDocument as String
Dim strWhere as String
strWhere = "1=1 "
stDocument = "rptYourReportName"
If Not IsNull(Me.cboCollector) Then
   strWhere = "[Collector] = """ & Me.cboCollector & """ "
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

DHookom
I have found that there are occasions when it is useful to base the report on a query, for use with OutputTo, for example and SendObject.

 

[dhookom]

Good point. I don't recall the last time I used (or was asked to encorporate) either of these but there are certainly questions about them so others use this functionality.

Duane
Hook'D on Access
MS Access MVP

 

[brendoaccess]

Bang on the money, dhookom - many thanks (sorry for delay - Christmas dinner at work)

And thanks to you too, Remou.

 

[FancyPrairie]

Although your criteria was pretty simple to handle, you may find in the future that your report criteria form may have more than just one combo box to choose from. For example, you may have several combo boxes, date ranges, option groups from which the user can choose from to filter the report results. Instead of writing a bunch of if statements to build the where clause for the OpenReport method, you could call one routine that will look at the selections the user made in each of the controls and return the where clause for you. This FAQ faq181-5497 contains that routine and explains how to use it. The directions on how to use it are explained in the header of the routine. The tag property of the combo box is the key.

In your case, for example, suppose your report query looked something like this:

Select Collector from MyTable;

The tag property of the combo box would look like this:

Where=MyTable.Collector,string;

That's all that is required. Now open your report like this:

DoCmd.OpenReport "ReportName", acPreview,,BuildWhere(Me)

The BuildWhere routine handles combo boxes, list boxes, date ranges, text boxes, check boxes, other ranges, and option groups. To make it work, simply open a new module and paste the code form the FAQ into the new module. Then define your tag properties correctly.