Expression

[mlack]

Hello All!

I am trying to get a query to run right with the Yes/No checkbox option. When the checkbox on the form is ON, I get only the ones that are checked (or YES). This also works for the unchecked box (which equals NO). What I really need is if checked, or YES, so only those items. But if unchecked (or NO), show mw all those items. A wildcard does not work (*). Any ideas? Here is my expression so far.

IIf([Forms]![User_Reports_FRM]![New_Order],Yes,No)

What needs to be added, replaced or changed to get the query to select ALL if unchecked?

Thx!

 

[mlack]

Sorry about the bad grammar..

"if checked, or YES, SEE only those items. But if unchecked (or NO), show ME all those items.

 

[psemianonymous]

Simply put, there is one simple way to do this.

Forget the IIf().


In your Yes/No field in the query designer, under "criteria", put:

[Forms]![User_Reports_FRM]![New_Order]



That way, if your New_Order checkbox is true, then the Criteria will state that the field in question must also be True. If it's unchecked, then the field in question must also be false.

 

[mlack]

Thanks for your response Foolio12. That is correct, but what I am looking for is if checked, YES, if unchecked, Yes AND No. I have tried the Yes and No statement but it only returns the no's when unchecked, which makes no sense. Any other ideas?

 

[psemianonymous]

In that case, make a calculated field in the query, let's call it

CalcField: [Forms]![User_Reports_FRM]![New_Order]


Under the Criteria, put on the first line, and the second line:

[tt]True
False[/tt]


Now scroll over to the Yes/No field in question. Put the Criteria for this field as "True" on the first line, LEAVE THE SECOND LINE BLANK.


If you want to know what this does exactly, look at the SQL version of the query once it's built. This will work.

 

[GoDawgs]

So you always want to return the "Yes"s? If so, then in your first line of criteria put "Yes" (just Yes, nothing else)...then in the line of criteria below it (or) put:

IIf([Forms]![User_Reports_FRM]![New_Order],Yes,No)

That way you always return Yes, and also return the No's if the iif passes. Hope that helps.

Kevin

 

[mlack]

Thanks Foolio for your responses, it worked! And also thx to you Dawg...your's worked to!!

I should have posted to this forum hours ago!