MS Access Query with IIF Statement 1

[pdigiglio]

I am trying to write a querry which selects records from table1 based on a numeric field called group. In the criteria section of the query design screen, under the field "group" I entered the following "IIF([Var] = 0, >0,[VAR])".

The values in the group field are 1,2,3,4 etc...

When running the query, you are prompted for the value of [Var]. If you enter 1, the querry returns all records from table1 where Group = 1. It works fine for Var=2,3,4 and so on.

The problem is if [Var] = 0, you would expect the query to return all records where group > 0, however it does not. The expression ">0" on its own in the criteria section does work and return all records but it does not seem to work in an IIF statement.


Im stuck.
[sig][/sig]

 

[Phooey]

Have you tried:

IIF([Var] = 0,[Var] > 0,[VAR])

I don't know much about using IIF statements but it certainly looks like it should work... [sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[Phooey]

ignore the strange character that looks like

 [sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[earme]

Have you tried entering in the name of the numeric field?

For example:
IIF([Var] = 0, group >0 , group = [VAR])

If it doesn't return the correct values, what does it return?

HTH
Evie

[sig][/sig]

 

[MichaelRed]

IIf([Var]=0,[Group],[var]) [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[MichaelRed]

More properly,

IIf([Var]=0,IIF([Group]> 0, [Group]),[var])

but the first will be &quot;o.k.&quot; as long there are no 'funnies' with group = 0 to contend with. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Robertd]

Hi pdigiglio

IIF([Var] = 0, &quot;like *&quot;,[VAR])

consider that the result of the iif statement becomes the critiera for the query as it does for 1,2,3,4,etc so if you want > 0 < to result in all records then > like * < as a critiera should do the trick ;-)

see ya
Robert


[sig][/sig]