another criteria syntax problem

[Milothicus]

here's what i have:

IIf([Forms]![frmJbBk]![chkIncmpltJB],>0)

it doesn't work. if i replace it with just >0 it works, but then i lose the option of hiding those records (obviously)

how do i format the >0 to get the iif output to work in the criterion?

 

[Milothicus]

little more info:

this is the whole criteria string that works:

>0 or IIf([Forms]![frmJbBk]![chkcmpltJB],0)

meaning if that checkbox is checked, i get the 0 values and the non-zero values (there are no negative values), and if it's not, i only get the non-zero values.

if i enter this:

IIf([Forms]![frmJbBk]![chkIncmpltJB],>0) Or IIf([Forms]![frmJbBk]![chkCmpltJB],0)

when i close the query and reopen, access has changed it to this (red bit added):

IIf([Forms]![frmJbBk]![chkIncmpltJB],([qryTotalValue].[Total])>0) Or IIf([Forms]![frmJbBk]![chkCmpltJB],0)

and i don't get any non-zero values at all, regardless of checkboxes. with this criterion, if either checkbox (or both) is/are checked, i get the zero values.

any ideas?

 

[FLabrecque]

Aren't you missing a clause for your IIF? Isn't it IIF(Boolean Expression, True Part, False Part)?

 

[mresann]

I agree. I don't see how the Iif functions are passing muster without getting an error response. Even the Access "corrected" formulae are incorrect.

Also, what is the context of where your code is operating. Is it on the query grid, on the form controls, or in a code module? And what is the name of the field that will be suppressed depending on the condition of the checkbox? You may not even need the Iif function.

 

[Jon K]

It seems you want the query to return [Total]>0 when the check box on the form is checked.

But it's not clear what you want the query to return when the check box is unchecked.
[Total]=0 ? [Total]>=0 ? or [Total]>=1 ?


Depending on what you want the query to return when the check box is checked and unchecked, in query Design View sometimes you may need to combine the IIF criteria with the field name in the field row.

Note: You don't need two check boxes for the same field.
.

 

[Milothicus]

i want one checkbox to determine wether or not the records with a 0 value are displayed, and the other to determine wether or not the records with a non-zero value are displayed, so i can show one or the other, or both, or (not likely to be used, really) none. i do need both checks because i'm splitting the records into two groups; zero, and non-zero, and i want to deal with each separately, but in the same column.

this is in the criteria section of the query definition.

 

[Milothicus]

it only took me 4 months to get this to work....

found the solution on this site, but can't remember which thread now...

instead of placing the iif statement in the criteria line, put it in the 'field' line of a new colum in the query, then set the 'show' line to false, and the criteria to true.

this way it runs the iif statement, and filters the records without placing the iif into the criteria line, because that seems to create all kinds of problems...

 

[Mile-O]

I believe you could also have used the Eval() function for the IIf() although I can't verify this.