[X]My Assistant

[Adsens]

Hi, I have a query with a Boolean function {IsFormLoaded} as criteria that checks if a form [Students] is open or not.
The criteria in the [ID_Tutor] of the query is :

CODE

= iif(IsFormLoaded("Students")=True;[Forms]![Students]![ID_Tutor];"")

the function works great, but the query still evaluates the ([Forms]![Students]![ID_Tutor]) even if the function returns a False.

Query's Message: Enter a parameter value ([Forms]![Students]![ID_Tutor])

my Function :

CODE

Public Function IsFormLoaded(strForm As String) As Boolean    
    IsFormLoaded = CurrentProject.AllForms(strForm).IsLoaded
End Function

Help Please
This post has been edited by Adsens: Oct 10 2018, 10:22 AM

[theDBguy]

Hi,

Since we're not familiar with the context where you're trying to run your query, can you please explain what your criteria is supposed to be doing? Also, what is the problem with the query still evaluating the criteria when the function returns false?

Thanks.

[projecttoday]

Please post the entire query code.

[cheekybuddha]

IIRC, IIf() evaluates both the TRUE and FALSE parts of the expression, irrespective of whether the expression returns true or false.

[Adsens]

@TheDBguy, @projectToday, @cheekybuddha

I want to do that :
If the form students is loaded then get the ID_Tutor from that form and put it as criteria of the query [ID_Tutor] field;
Else if the form is NOT loaded then put nothing in the criteria and get all records.

My query is :

CODE

SELECT Tutors.ID_Tutor, Students.FName, Students.LName
FROM Tutors INNER JOIN Students ON Tutors.ID_Tutor = Students.TutorID
GROUP BY Tutors.ID_Tutor, Students.FName, Students.LName
HAVING (((Tutors.ID_ Tutor)=IIf(IsFormLoaded("Students")=True,[Forms]![Students]![ID_Tutor],"")));

[cheekybuddha]

Just to illustrate the point:

CODE

Function Dummy1() As Boolean

  Debug.Print "Hello"
  Dummy1 = True
  
End Function

Function Dummy2() As Boolean

  Debug.Print "World"
  Dummy2 = False
  
End Function

Then, in the Immediate Window:

CODE

?IIf(True, Dummy1, Dummy2)
Hello
World
True
?IIf(False, Dummy1, Dummy2)
Hello
World
False

If you put the IIf() expression in a query then 'Hello' and 'World' are both printed to the Immediate Window as well.

hth,

d

[theDBguy]

Hi Adsense,

Ah, okay, that makes more sense now. Thanks for the clarification. As was already mentioned, the IIf() statement is evaluating both the True and False arguments, so you were probably getting an error in your criteria. What you could do instead is either modify your IsLoaded() function or create a new one to use as the new criteria in place of the IIf() statement. This new function should simply return the value from the form or nothing (null) True.

Just my 2 cents...

[cheekybuddha]

@Adsens,

Since you are incorporating VBA with the IsFormLoaded() function, just create another function that will return the ID_Tutor, but makes the evaluation in VBA.

Something like:

CODE

Function GetTutorID() As String

  Dim strRet As String

  If IsFormLoaded("Students") Then
    strRet = Forms.Students.ID_Tutor
  End If

  GetTutorID = strRet

End Function

Then, in you query:

CODE

SELECT Tutors.ID_Tutor, Students.FName, Students.LName
FROM Tutors INNER JOIN Students ON Tutors.ID_Tutor = Students.TutorID
GROUP BY Tutors.ID_Tutor, Students.FName, Students.LName
HAVING Tutors.ID_Tutor=GetTutorID();

(Is field Tutors.ID_Tutor a string or numeric datatype? If numeric the above function will have to be modified)

hth,

d

[projecttoday]

HAVING is for aggregate functions. If it's not an aggregate function use WHERE.

Under what conditions would that form not be open? Doesn't [Forms]![Students]![ID_Tutor] cause an error in any case if the form isn't open?

Maybe WHERE Not CurrentProject.AllForms(strForm).IsLoaded OR Tutors.ID_ Tutor=[Forms]![Students]![ID_Tutor].

[Adsens]

Thank you guys for your clarifications.
I think the best way to get it is the way that @theDBguy mentioned (New function).
Best Regards
This post has been edited by Adsens: Oct 11 2018, 03:32 AM