Simplifies SQL is

SELECT Questions.Question
FROM Questions
WHERE
(((Questions.QuestionID)=IIf(IsLoaded("Questions") ,[Forms]![Questions]![QuestionID],[Questions]![QuestionID])))
GROUP BY Questions.Question;

The full query is more complex, but irrelevant for this question

IsLoaded("FormName") simply returns true or false depending whether the form
is open.

If the form is open, all works as expected giving only the same question on
the form. If the form is closed, The parameter box comes up asking for a
value for Forms!Question!QuestionID, and regardless of what I put in there,
including just pressink the OK button, all the records in the query show as
they should ie 200 odd questions. Why is the "Enter Paramater Box" showing? -
more importantly, how do I stop it showing?

Thanks Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:ic9lje$78d$1 (AT) speranza (DOT) aioe.org:

IIf() evaluates the 3 passed parameters, whether or not the first
parameter evaluates true or false.



--
Bob Q.
PA is y I've altered my address.

On 21/11/2010 14:09:10, Bob Quintal wrote:

Sorry, I must be a bit thick. What 3rd parameter. Only options are
[Forms]![Questions]![QuestionID] or [Questions]![QuestionID]

Phil

Bob Quintal wrote:


That's true for the VBA IIf function, but not for the
Expression Service IIf. A simple test could be something
like:
IIf(False, MsgBox("True"), MsgBox("False"))

Unfortunately, that does not help explain why Phil is being
prompted. Unlikely as it is, maybe it's because of the use
of ! instead of dot or all the useless parenthesis or
there's something funny in the IsLoaded function or ...??

--
Marsh

On 21/11/2010 19:02:47, Marshall Barton wrote:
Thanks Marsh

Is loaded has worked for years and is correctly giving 0

Have simplified even further to
SELECT Questions.Question, IsLoaded("Questions") AS Expr1
FROM Questions
WHERE
Questions.QuestionID=IIf(IsLoaded("Questions"),[Forms].[Questions].[QuestionID],[Questions].[QuestionID])

and get same result. Changing "!" to "." doesn't help either

Phil

Phil wrote:


That should not work at all. You replaced one too many !

It should have been:

WHERE Questions.QuestionID = IIf(IsLoaded("Questions"),
Forms!Questions.QuestionID, Questions.QuestionID)

But I finally followed your example closely enough to
reproduce the behavior. After some head scratching and more
tests, I conclude that it the fact that
Forms!Questions.QuestionID is a query parameter and that the
prompt occurs before the query starts running. The IIf has
nothing to do with it.

I think you can work around it by not using a parameter and
calling a custom function in a standard module that returns
the ID from the form:

Public Function GetQID()
GetQID = Forms!Questions.QuestionID
End Function

and

WHERE Questions.QuestionID = IIf(IsLoaded("Questions"),
GetQID(), Questions.QuestionID)

--
Marsh

On 22/11/2010 04:19:29, Marshall Barton wrote:
I think your theory is right that parameter bit is called before the IIf
clause is evaluated, and the solution you suggested has the same problem.

However this put me on the right track and

Public Function GetQID(StgFrm As String, StgFld As String)

If IsLoaded(StgFrm) Then
GetQID = Forms(StgFrm)(StgFld)
End If

End Function

WHERE Questions.QuestionID
=IIf(IsLoaded("Questions"),GetQID("Questions","Que stionID"),[Questions].[QuestionID])

Thanks everso

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:icbnvo$j31$1 (AT) speranza (DOT) aioe.org:

iif() takes 3 parameters,
the first gets evaluated to true or false,
the second is returned if the first is true,
the third is returned if the first is false.

--
Bob Q.
PA is y I've altered my address.

On 22/11/2010 22:33:18, Bob Quintal wrote:
I said I was being a bit thick. See what you mean

Anyway, problem sorted thanks

Phil