Query Criteria IIf statement

[ian]

I'm using MSAccess 2003 and this seems like a simple process, especially
compared to some of the things I'm trying to do in my database. In the
criteria of a query field, I am using the following IIf statement:

IIf([FYAGR]>3000000,3,<4)

[FYAGR] is a different field in the same query. When [FYAGR] is greater
than 3,000,000, I get 3 as expected, but no matter how I word the range of
numbers I'm looking for in the false part of the statement, my query returns
no records. If I remove the IIf statement and place '<4' I get the correct
range, but no matter the combination I can't seem to get the desired criteria
out of the IIf statement. I can also place a single digit in my false part
and return that digit with no problems. Is this a limitation of the IIf
statement that I am unaware of? Maybe there is something simple I'm missing,
but any help would be appreciated.

 

[Stefan Hoffmann]

hi Ian,

IIf([FYAGR]>3000000,3,<4)

IIf() can only return a value, not an expression nor an expression part.

Maybe there is something simple I'm missing, but any help would be appreciated.

A precise question?

I guess your trying to solve something like

SELECT *
FROM yourTable
WHERE [fieldA] = IIf([FYAGR]>3000000,3,<4);

Do you? In this case you can use it like this

SELECT *
FROM yourTable
WHERE IIf([FYAGR]>3000000, [fieldA] = 3, [fieldA] < 4);


mfG
--> stefan <--

 

[Maarkr]

are you trying to return a text value ? "<4"

 

[John W. Vinson]

I'm using MSAccess 2003 and this seems like a simple process, especially
compared to some of the things I'm trying to do in my database. In the
criteria of a query field, I am using the following IIf statement:

IIf([FYAGR]>3000000,3,<4)

[FYAGR] is a different field in the same query. When [FYAGR] is greater
than 3,000,000, I get 3 as expected, but no matter how I word the range of
numbers I'm looking for in the false part of the statement, my query returns
no records. If I remove the IIf statement and place '<4' I get the correct
range, but no matter the combination I can't seem to get the desired criteria
out of the IIf statement. I can also place a single digit in my false part
and return that digit with no problems. Is this a limitation of the IIf
statement that I am unaware of? Maybe there is something simple I'm missing,
but any help would be appreciated.

You can't pass an operator such as < in an IIF statement - only an actual
value.

Try instead a criterion of

WHERE (([FYAGR] > 3000000 AND [thisfield] = 3) OR (FYAGR] <= 3000000 AND
[thisfield] < 4)

 

[ian]

Both of these solutions accomplished exactly what I was looking for! Thanks!