Query Criteria IIf statement


I

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.
 
Ad

Advertisements

S

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 <--
 
J

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top