Question Query to return Not Zero in criteria

[chuckcoleman]

I have a query and one of the fields will return either a 0, (zero), or any other number. I want criteria in the query using an If Then statement to reflect that. For instance, iif([FieldX] = 0,0, Not 0). In the case if FieldX has a value of 0 then I want that included in the results. If FieldX has a value of 25, 59 or any other number other than zero I want those records displayed.

I've tried a lot of different things but none of them work for the Not 0 requirement.

Any ideas?

 

[DCrake]

IIF([FieldX] > 0, True,False)

Which means if there is a value in fieldX that is greater than zero True otherwise False

Obviously you can replace the True,False with anything you want it to say.

 

[chuckcoleman]

DCrake,

Thanks. In your example you said to replace True & False with whatever. So, if it says, IIF([FieldX]=0,0,I can't figure this out). The "I can't figure this out" is how do I express any number other than a 0? I've tried, IIF([FieldX]=0,0,Not 0), IIF([FieldX]=0,0,Not Like 0), IIF([FieldX]=0,0,Nz (0))

 

[Brianwarnock]

In the case if FieldX has a value of 0 then I want that included in the results. If FieldX has a value of 25, 59 or any other number other than zero I want those records displayed.
Any ideas?

I may be going senile but this says to me if zero select if not zero select.

Brian

 

[DCrake]

Like Brian I am at a loss at to your actual requirement. If you only want to show records that have value other than 0 then in your condition line in the query use the syntax <> 0. There is no need for any IIF() statements.

 

[chuckcoleman]

I should have explained a little better. I have a form with a Frame on it with 2 toggle buttons. If you click one button on the Frame it returns a value of 0. If you click the other button on the Frame, it returns a value of 1. In the Query, I want to display records where FieldX has a value of 0 if the user clicks on the first button. Easy. That works. If the user clicks on the second button I want records returned where the value of FieldX is anything OTHER than a 0. Th

 

[DCrake]

What code have you got behind the click event of the frame? This may help greatly.

 

[chuckcoleman]

It's a macro that says:

If [Frame118] = 0 then
Name = WhatsFrame
Expression = 0
End If

If [Frame118] = 1 then
Name = WhatsFrame
Expression = 1
End If

In the query criteria for FieldX I say:

iif([Tempars]![WhatsFrame]=0,0, this is what I'm struggling with)

 

[chuckcoleman]

In the previous post I should have made it more clear that I was using SetTempVar in the macro. Sorry.

 

[Brianwarnock]

I think this must be 2007 as i don't understand some of the lingo. I would have just had two buttons
1 running a query with =0 and 2 with <>0
but then I am a simple soul.

Brian

 

[chuckcoleman]

Brian,

I'm using Access 2010, (very similar to 2007). You can't use anything but a number in the toggle frame. I input <>0 and you get a message, "The value you entered isn't valid for this field." Therefore, I'm still searching for an answer.