Combo Box - Returning all records when select "All" 2

[Mack2]

For a combo box, I have a value of "All". Below the combo box, is a button to open a report. When the button is clicked it opens a report that looks at the combo box for the value. When the user selects the "All" value from the combo box, I would like all values returned from a field for the report. Please help. THANKS!

 

[fneily]

You have to test for the All condition. As in the example below, I tested the combobox value for All then set a variable to a SQL statement. Then I used that in the WClause parameter for the DoCmd.OpenReport method.

If Me![countycombo].Value = "<All_Counties>" Then
holdcnt = "([County] Like " & "'*' Or [County] Is Null)"
Else
holdcnt = "[County] = " & "'" & Me![countycombo] & "'"
End If

WClause = holdcnt & " And " & holdzone & " And " & holdstat & " And [ParcelSize] < " & holdacre & ""

DoCmd.OpenReport _
ReportName:=RName, _
WhereCondition:=WClause, _
view:=acViewPreview

 

[Mack2]

That looks like what I need. What does the following line do?
& " And " & holdzone & " And " & holdstat & " And [ParcelSize] < " & holdacre & ""

 

[mp9]

For more, and an alternative take on adding ALL to the top of a combo box, check out

http://www22.brinkster.com/accessory/forms/007.shtml

 

[Mack2]

The code worked, thanks fneilly! I would prefer to use an iff statement within a query like the following;

IIf([Forms]![Main Menu]![EngSelectCombo]="All",([tblCAR].[EngTracking]) Like "*"",[Forms]![Main Menu]![EngSelectCombo]")

When I use this code, it does not work. Have you ever used the above way?

 

[fneily]

IIf([Forms]![Main Menu]![EngSelectCombo]="All",([tblCAR].[EngTracking]) Like "*"",[Forms]![Main Menu]![EngSelectCombo]")

Just looking at it and it seems you have a too many " after *" and a " at the end. So;

IIf([Forms]![Main Menu]![EngSelectCombo]="All",([tblCAR].[EngTracking]) Like "*",[Forms]![Main Menu]![EngSelectCombo])

Haven't tested it though.

 

[PHV]

In the EngTracking criteria cell:
=[Forms]![Main Menu]![EngSelectCombo] OR [Forms]![Main Menu]![EngSelectCombo]='All'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mack2]

Did not work. Don't you have to have a "*" in the formula

 

[PHV]

Did not work
Error message ? computer crash ? unexpexted result ? ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mack2]

WOW. I take it back it works. I do not understand how it created another field called;
[Forms]![Main Menu]![EngSelectCombo] and then put 'All' for the criteria. Is this what it is suppose to do?

THANKS!!!