Help with IIF Statement

[dzirkelb]

Here is my IIF statement:

WHERE IIF(('::GreatTotalScore::'=''), True, TotalScore>=::GreatTotalScore:
ANd IIF('::LessTotalScore::'=''), True, TotalScore <= ::LessTotalScore:

This works correctly. It is pulling the data from a query made in MS Access
for the total score of numerous fields. However, It only works if both
criteria are put in, meaning, I can't leave GreatTotalScore or LessTotalScore
blank or it will produce an error stating missing operator.

I have made numerous IIF statements in the past all identical to this one;
however, they have all been for text fields. If I change this statement to
the following:

WHERE IIF(('::GreatTotalScore::'=''), True,
TotalScore>='::GreatTotalScore::')
ANd IIF('::LessTotalScore::'=''), True, TotalScore <= '::LessTotalScore::')

(added single quotes to 2nd portion of IIF statemtn on both).

That statement works also for everything MINUS scores of 100...it reads them
as 1 instead of 100 (cause its seeing it as text).

Does anyone know how I can repair this statement to work like the text
version so it skips the input box if its null AND work like the numeric
version to see a score of 100 as 100? Thanks!

 

[Stefan B Rusynko]

You don't appear to be using IIF correctly to return a boolean result based on evaluating an expression (which must be either a
boolean field or an expression that evaluates to a boolean result)

IIf(expression, truecondition, falsecondition)

If both your fields GreatTotalScore AND LessTotalScore <> '' (both are not empty),
Your WHERE evaluates to: "WHERE True AND True"
Or w/o the AND part to "WHERE True"
- an invalid Where condition (you need to replace the "True" parts w/ a valid Where condition)
- change the "True" to say: TotalScore<::GreatTotalScore::

If both fields GreatTotalScore AND LessTotalScore = '' (both are empty),
Your WHERE evaluates to:
"WHERE TotalScore>=GreatTotalScore AND TotalScore <= LessTotalScore"
- works only because you don't have the conditions where both values (GreatTotalScore AND LessTotalScore) are empty

So your statement should be more like
WHERE IIF( ('::GreatTotalScore::'=''), TotalScore<::GreatTotalScore::, TotalScore>=::GreatTotalScore:

If you really need to test both conditions for empty values the simple IIF would be:

IIf((GreatTotalScore='' AND LessTotalScore=''), TotalScore<GreatTotalScore AND TotalScore>LessTotalScore,
TotalScore>=GreatTotalScore AND TotalScore<=LessTotalScore)

Also see
http://www.aspemporium.com/codelib.aspx?pid=92&cid=7
for declaring IIF as a function



| Here is my IIF statement:
|
| WHERE IIF(('::GreatTotalScore::'=''), True, TotalScore>=::GreatTotalScore:
| ANd IIF('::LessTotalScore::'=''), True, TotalScore <= ::LessTotalScore:
|
| This works correctly. It is pulling the data from a query made in MS Access
| for the total score of numerous fields. However, It only works if both
| criteria are put in, meaning, I can't leave GreatTotalScore or LessTotalScore
| blank or it will produce an error stating missing operator.
|
| I have made numerous IIF statements in the past all identical to this one;
| however, they have all been for text fields. If I change this statement to
| the following:
|
| WHERE IIF(('::GreatTotalScore::'=''), True,
| TotalScore>='::GreatTotalScore::')
| ANd IIF('::LessTotalScore::'=''), True, TotalScore <= '::LessTotalScore::')
|
| (added single quotes to 2nd portion of IIF statemtn on both).
|
| That statement works also for everything MINUS scores of 100...it reads them
| as 1 instead of 100 (cause its seeing it as text).
|
| Does anyone know how I can repair this statement to work like the text
| version so it skips the input box if its null AND work like the numeric
| version to see a score of 100 as 100? Thanks!