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

 

[Pat Hartman]

I'm not sure what you are looking for since your example doesn't make logical sense but compound conditions MUST repeat the field being tested.

IIf(IsNull(FieldA) Or FieldA > FieldB Or FieldA < FieldC, true expression, false expression)

 

[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

 

[Pat Hartman]

You are attempting to change the structure of a query by using an expression. This is not possible. You can however, build the entire SQL string in VBA
Dim strSQL = String
strSQL = "Select ... FRom .... Where " & 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.....) & ";"

Notice that I surrounded the string in the true path with quotes. This string is what you want added to the end of your select statement. Since building a string this way can be difficult, I suggest a stop at the next statement so you can examine the contents of strSQL and make sure that it is a valid SQL string. You can even print it in the debug window and copy it to the query designer window and paste it there to test it.

 

[BrianB75]

Thanks Pat!!

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

Brian