Operators in IIF Statements

[BrianB75]

Is it possible to have operators (<, >, <=, >=) in an IIF statement like this:

iif([field a] is null, > [field b], < [field c])

This is in the criteria for a currency field.

I have been trying different combinations without any luck for the last couple of hours or so.

Any help would be greatly appreciated.

Thanks in advance,
Brian

 

[BrianB75]

Sorry, should have used a more specific example. Basically I am searching a Fee field in another table (field name is cd_fee) where the user can either see all of the values, all of the values less than an input amount, all of the values greater than an input amount or a range of values between two amounts.

The input fields are [fee_less_than] and [fee_greater_than] and I am using the iif statement to determine which of the four searches above to execute. If both the input fields are $0, default value, then it will pull back all records from the table. If one field is 0 and the other is >0 then it will run either the second or third search from above:


iif([fee_less_than]=0 and [fee_greater_than]=0,[cd_fee], iif([fee_less_than]=0 and [fee_greater_than]>0,[cd_fee]>=[fee_greater_than],iif.....


The first case works fine but as soon as I introduce the operator in the than area (bolded), the query blows up and pulls back no records.

I can accomplish this in a VBA module but the project is for a manager in another department who wants to learn how this all works and I'd rather not overload him with Access basics (tables, queries, etc....) and VBA at the same time.

Thanks again,
Brian

 

[BrianB75]

Thanks Pat!!

I didn't think it was possible but I wanted to try. Of to the module I go.

Brian