qry by form with combo box contining 'all'

[MelB]

I have a form that drives a query for a report. There is a combo box with three choices for a field to filter on. It works great, except that now that the user has seen it, he wants the combo to have an option for 'All'. I used a Union query to add 'All' as an option but now I don't know how to make the query work properly. The underlying query has [forms]![formname]![fieldname]. If they pick one of the three valid choices for the field it work fine but if they pick 'All' it doesn't. I understand why (none of the records contain the text 'all') but I don't know how to fix it. Is this possible?

 

[RichO]

Here one suggestion:

Add a textbox control (non-visible) to your form and reference this new control from your query as the criteria in this way:

Like "*" & [Forms]![FormName]![txtThisNewTextbox] & "*"

Then in the After Update event of your combo box:

If cboMyCombo = "All" Then
     Me.txtThisNewTextbox = ""
Else
     Me.txtThisNewTextbox = Me.cboMyCombo
End If

This will pass the value from the combo box to the new control, and if it is "All" it will pass an empty string which will return all records as long as you use "Like" and the wildcard "*".

 

[Jon K]

Alternatively, you can put this in a new column in the query grid (replacing with the correct FieldName):-

Field: [forms]![formname]![ComboBoxName]="All" Or [FieldName]=[forms]![formname]![ComboBoxName]

Show: uncheck

Criteria: <>False

 

[MelB]

Thanks to both of you. I tried RichO's first and it works great. Thanks!!!