Field criteria selection problem

[Al]

I have a query that contains a calculated field that can have a value of 1,
2, or 3. It's statement looks like this:
Ind:
IIf([Forms]![frmSelectMenu]![strYear]="2004",1,IIf([Forms]![frmInput]![optNacct]=True,3,2)). It works fine.

I would like to use the results of [Ind] as a selection criteria for another
table field [Blvl]. The selection criteria for this field looks like this:
iif([Ind]=1,>=1 and <=4,>=1)

I can set the criteria to either ">=1 and <=4" or ">1" and this works. When
I include the iif statment I get errors (too complex) and I am prompted for
the value of [Ind].

Im sure this doesn't translate to SQL well. Is there a way to either set an
SQL where statement to do this or set it up another way?

 

[John Vinson]

I would like to use the results of [Ind] as a selection criteria for another
table field [Blvl]. The selection criteria for this field looks like this:
iif([Ind]=1,>=1 and <=4,>=1)

I can set the criteria to either ">=1 and <=4" or ">1" and this works. When
I include the iif statment I get errors (too complex) and I am prompted for
the value of [Ind].

Im sure this doesn't translate to SQL well. Is there a way to either set an
SQL where statement to do this or set it up another way?

You can only pass values in an IIF - not operators such as <=.

Try using OR logic:

WHERE ([Ind] = 1 AND [Blvl] =1)
OR ([Ind] = 2 AND [Blvl] >= 1 AND [Blvl] <= 4)
OR ([Ind] = 3 AND [Blvl] >= 1)


John W. Vinson[MVP]