Stefan
Wed Dec 22 05:23:24 CST 2004
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
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________
"dzirkelb@hotmail.com" <dzirkelbhotmailcom@discussions.microsoft.com> wrote in message
news:4FAB4755-7C08-4E5B-9F89-3CEC05D81FF1@microsoft.com...
| 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!