Check box passes its status to a Query

[MarkRobinson]

Annoying problem has me stumped:<br>In the criteria box of a select query I have:<br>IIf([Forms]![AChg]![BalDue]=0,,&gt;0)<br>[BalDue] is a check box.<br>I want the criteria to be &quot;&gt;0&quot; if the check box is checked, otherwise show all. It will work if I put values in, I can show records = to a specific # IIf([Forms]![AChg]![BalDue]=0,150,250) but the Greater Than seems to get lost.<br>

 

[DougP]

Looks like you need a value or something in the &quot;Equals side&quot;. OK, it's is blank.<br><br>The IIf statement works like this:<br>IIf (a value is something, then use this result casue the equation is true , else this result casue it's false)<br><br>you need this<br>IIf([Forms]![AChg]![BalDue]=0,[Forms]![AChg]![BalDue],0)<br><br>Which says<br>&nbsp;if ([Forms]![AChg]![BalDue]is equal to zero, then make it [Forms]![AChg]![BalDue], else make it zero)<br><br>Now you may have to switch the fields I put around but you get the idea<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[MarkRobinson]

I'm with you so far... I can definately get any value I want... what I'm having trouble with is getting the criteria to be GREATER THAN an amount.<br><br>Iif(expression,0,&gt;0) should work, shouldn't it. I would want it to put &gt;0 in the criteria. <br><br>

 

[Guest_imported]

on the first post you say &quot;otherwise show all&quot;<br>IIf(expression,0,&gt;0) will only show 0 or greater then 0<br><br>if you truely want to show all you will need to one of these two<br><br>Like IIf([Forms]![AChg]![BalDue]=0,0,&gt;=0)<br><br>Like IIf([Forms]![AChg]![BalDue]=0,0,&quot;*&quot<br><br><br>

 

[MarkRobinson]

The &quot;*&quot; returns an error message &quot;This expresseion is either types incorrectly or is too complicated to be evaluated.&quot;<br>The &gt;=0 solution just returns one zero value record.<br>It's access 2000.<br>It won't seem to recognize the Greater than.<br>??????????

 

[Guest_imported]

is the field you are doing this criteria in a number or currency field? <br>did you place the Like in front of the code?<br><br>forget the iif function for now! <br><br>in the query, what do you type in the criteria to get the records you want to see if the the check box on the form were not checked.<br>now what criteria would you put if it were checked.<br>now <br>Like IIf([Forms]![AChg]![BalDue]=0,the first criteria here,the last criteria here)<br>this is assuming the [BalDue] is a checkbox like you said<br>good luck<br>

 

[MarkRobinson]

To get the records I want if the box is checked I type <br>&gt;0 <br>in the criteria field <br>(the name of the field is [CRAFT]![Balance Due]<br>It was a currency field... I tried non-currency<br><br>This is what I typed in <br>Like IIf([Forms]![Detail Assessment charges]![BalDueOnly]=0,&quot;*&quot;,&gt;0)<br><br>Access added the field name<br>Like IIf([Forms]![Detail Assessment charges]![BalDueOnly]=0,&quot;*&quot;,([CRAFT].[Balance Due])&gt;0)<br><br>Shows all records if check box [Forms]![Detail Assessment charges]![BalDueOnly] is blank. If box is checked, I see only the records where [CRAFT]![Balance Due]=0<br><br>The &gt; is ignored.<br><br>The Like allows the &quot;*&quot; to work (without the Like the &gt;0 is treated like 0 and the &quot;*&quot; causes an error.)<br><br>THIS IS DRIVING ME NUTS!<br><br>

 

[Guest_imported]

First my apologies I did not test the code before posting I assumed this was a simple problem. I now feel your pain.<br>The solution I came up with is not the best but it do work<br><br>Between 0 And IIf([Forms]![AChg]![BalDue]=0,0,1000)<br><br>you will want to set the last field to the max amount you will expect to find in [baldue].<br>

 

[MarkRobinson]

YOU'RE BRILLIANT!&nbsp;&nbsp;... or I'm an Idiot.<br>Final solution is:<br>Between (IIf([Forms]![Detail Assessment charges]![BalDueOnly]=0,-99999,[Forms]![Detail Assessment charges]![BalDueOnlyAmt])) And 99999<br>Not very elegant, but it works. Why is this so hard!<br><br>if [baldueonly] checked, then it only lists the owners with balance due greater than the amount typed in the [baldueonlyamt] box.<br><br>Thanks!<br>