UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query With A Boolen Function, Access 2016    
 
   
Adsens
post Oct 10 2018, 10:21 AM
Post#1



Posts: 16
Joined: 29-November 17



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 confused.gif
This post has been edited by Adsens: Oct 10 2018, 10:22 AM
Go to the top of the page
 
theDBguy
post Oct 10 2018, 10:40 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Oct 10 2018, 11:06 AM
Post#3


UtterAccess VIP
Posts: 10,150
Joined: 10-February 04
From: South Charleston, WV


Please post the entire query code.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Oct 10 2018, 11:34 AM
Post#4


UtterAccess VIP
Posts: 10,428
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
Adsens
post Oct 10 2018, 11:39 AM
Post#5



Posts: 16
Joined: 29-November 17



@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],"")));

Go to the top of the page
 
cheekybuddha
post Oct 10 2018, 11:45 AM
Post#6


UtterAccess VIP
Posts: 10,428
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Oct 10 2018, 11:48 AM
Post#7


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Oct 10 2018, 11:53 AM
Post#8


UtterAccess VIP
Posts: 10,428
Joined: 6-December 03
From: Telegraph Hill


@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

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Oct 10 2018, 02:05 PM
Post#9


UtterAccess VIP
Posts: 10,150
Joined: 10-February 04
From: South Charleston, WV


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


--------------------
Robert Crouser
Go to the top of the page
 
Adsens
post Oct 11 2018, 03:23 AM
Post#10



Posts: 16
Joined: 29-November 17



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


Custom Search
RSSSearch   Top   Lo-Fi    16th October 2018 - 10:45 PM