Sdeam
asked on
Using an IIF statement in Query criteria
I am making a query that selects its data criteria based on input from a form. The form has a 3 button toggle to select 1)All orders, 2)Open Orders, or 3)Closed Orders. I then have an IIF statement in the field OrderClosed (a yes no check) criteria as follows
IIF(Forms![frmOrderReport]
The problem that I am having is that the first case somehow evaluates to be false. I would like to be able to have the criteria be turned off if the Toggle State is selected as 1 (meaning that all orders should be shown.)
I have run into this problem on another query in which I am selecting customers by address (some of which may not have an address entry -- so again I would like to be able to turn the criteria off in the event that there was not an address entered. Some contacts are phone or email only so it can still be a valid entry without an address but if the criteria searches for a matching address and one was not entered then the customer does not show up at all.
I have tried to set the criteria to a "". But this only searches for an empty string which still does not turn up the right records. I have also tried, in the first example, to use numbers instead of the True/False constants. In attempting to set the criteria by numbers I have used absolutes and ranges.
IIF(Forms![frmOrderReport]
The problem that I am having is that the first case somehow evaluates to be false. I would like to be able to have the criteria be turned off if the Toggle State is selected as 1 (meaning that all orders should be shown.)
I have run into this problem on another query in which I am selecting customers by address (some of which may not have an address entry -- so again I would like to be able to turn the criteria off in the event that there was not an address entered. Some contacts are phone or email only so it can still be a valid entry without an address but if the criteria searches for a matching address and one was not entered then the customer does not show up at all.
I have tried to set the criteria to a "". But this only searches for an empty string which still does not turn up the right records. I have also tried, in the first example, to use numbers instead of the True/False constants. In attempting to set the criteria by numbers I have used absolutes and ranges.
Maybe try this:
IIF(Forms![frmOrderReport]
This way the second IIF is evaluated only if the first if False.
Ken
IIF(Forms![frmOrderReport]
This way the second IIF is evaluated only if the first if False.
Ken
just a guess, but i think the problem is with the "greyed" out toggle. the "greyed" out toggle equates to a NULL. test for null on the toggle by using IsNull or the Nz method.
dovholuk
dovholuk
ASKER
The toggle has a default value of 1 so it will never be null. Also, the OrderClosed field has a default value of False so it should never be null either. (I have had numerous problems with leaving important fields with a null value so I take a pre-emptive approach to it wherever possible)
I already have the IIF statement in the form to only evaluate the second have if the first expression is evaluated to be false. Is there anyway to set up a criteria such as
IIF([expression] = true, [criteria here], [turn off all criteria])
If I knew how to do this type of criteria statement then I could shape the IIF statement correctly. For the first example I gave:
IIF(Forms![frmOrderReport]
= 2,False,True))
the toggle state has 3 possible choices the IIF statement only shows two because if it is not one of the first two then it can safely be assumed to be the third choice (closed orders). For clarification, the Control ctrlToggleState is an unbound textbox that pulls the current state of a 3 button toggle contained inside of a frame.
I already have the IIF statement in the form to only evaluate the second have if the first expression is evaluated to be false. Is there anyway to set up a criteria such as
IIF([expression] = true, [criteria here], [turn off all criteria])
If I knew how to do this type of criteria statement then I could shape the IIF statement correctly. For the first example I gave:
IIF(Forms![frmOrderReport]
= 2,False,True))
the toggle state has 3 possible choices the IIF statement only shows two because if it is not one of the first two then it can safely be assumed to be the third choice (closed orders). For clarification, the Control ctrlToggleState is an unbound textbox that pulls the current state of a 3 button toggle contained inside of a frame.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the check box is not a three state -- it is just a yes/no. If you mean the toggle state -- I am storing the (temporarily) in a text box.
I do not quite underst what you mean by 'the
field OrderClosed (a yes no check) criteria as follows '.
I presume you have set criteria in the underlying query or Form filter. In that case maybe I undesrtand your problem -indeed criteria 'Field = True OR False' will not return BOTH True and False values, but only True. So again
>The problem that I am having is that the first case somehow evaluates to be false
this statement sounds quite strange, you should get only True values
Correct version will be 'Field = True OR Field = False', or if you need your field name mentioned only once utilize fact that True = -1 and False = 0 , i.e. use ' Field > -2 ' which will return both true and false values.
Paradoxically using 'Field = False OR True' will remove ALL filtering too (again the second condition True is evaluated on its own resulting that the entiring criteria evaluates to True for any value the Field, but I do not think it's worth using such hardly comprehensible coding.
HTH
field OrderClosed (a yes no check) criteria as follows '.
I presume you have set criteria in the underlying query or Form filter. In that case maybe I undesrtand your problem -indeed criteria 'Field = True OR False' will not return BOTH True and False values, but only True. So again
>The problem that I am having is that the first case somehow evaluates to be false
this statement sounds quite strange, you should get only True values
Correct version will be 'Field = True OR Field = False', or if you need your field name mentioned only once utilize fact that True = -1 and False = 0 , i.e. use ' Field > -2 ' which will return both true and false values.
Paradoxically using 'Field = False OR True' will remove ALL filtering too (again the second condition True is evaluated on its own resulting that the entiring criteria evaluates to True for any value the Field, but I do not think it's worth using such hardly comprehensible coding.
HTH
dovholuk is correct
TRUE = -1
FALSE = 0
GREY = NULL
and for IIF-statements, it wont work !
TRUE = -1
FALSE = 0
GREY = NULL
and for IIF-statements, it wont work !
Sdeam:
Did you receive an adequate answer? If not, please post again.
Thanks!
Joe
Did you receive an adequate answer? If not, please post again.
Thanks!
Joe
Sdeam's last login was late-October.
Hello everyone.
We are cleaning up this topic area. This question is considered to be *outdated* and we would like you to move on with it.
Toward that end, we graciously request the following:
-----Askers-----
*Do NOT award points to me.
*Stay active in your question. If you asked this question and have not yet responded to the comments given by experts, please do so as soon as possible, even if it is just to say "I haven't been able to try that yet." If there are many comments, please address them all. If the suggestions given did not help, please give particulars such as error messages or other events that may have occurred. If the experts have asked questions, please respond to them to help them find a resolution to your issue. Please do not ignore them.
*If this issue remains unresolved, you may want to post a new, 0-point question here in this topic area with a link to this question in it to attract other experts.
*Award points for solutions. If one of the suggestions did work or if an expert put in a good effort toward resolving your issue, please award the points to that expert by clicking on the button above that expert's post that says "Accept comment as answer".
*If you have resolved this issue in another manner, please share it with us. We will refund your points and store the question in the database so others might get value from it.
*If you have given up on this issue, and prefer to delete it, you can request that, but the experts may choose to argue that they have worked hard to help you, and the moderator will make final determination if necessary.
*Most importantly, be fair in your grading. Please see guidelines for grading at
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
If you give any grade less than an A without stating why, the grade will be changed upon request by the expert.
Thanks very much for being fair to our experts.
-----Experts-----
*Do not provide further support to this question unless the Asker responds that it is not yet resolved and wishes to continue to get help.
*If you truly believe that your answer is the best one that has been provided, please state which comment you made that you believe to be correct. If no one argues your comment, you will be awarded the points.
*If you would like to *vote* for another expert's comment, feel free to do so. Likewise, feel free to argue (nicely) any points that other experts make. We want everyone's input to be considered. Unless it is really unavoidable, vote only for one expert's response--it is quite a bit of work to split points, and many people come and go from the site and never see the "Points for" questions.
*Please don't thank the moderator for granting points to you. This only causes more email for the moderator, and I can personally attest to literally 100s of emails a day. Your thoughts are no less appreciated! We are experts too.
*Finally, if you ever question a moderator's determination, don't hesitate to do so right in the question in question. If you feel you want to voice your comment privately, email me at amp@experts-exchange.com.
Your input is extremely valuable and very much appreciated.
thank you
amp
community support moderator
Hello everyone.
We are cleaning up this topic area. This question is considered to be *outdated* and we would like you to move on with it.
Toward that end, we graciously request the following:
-----Askers-----
*Do NOT award points to me.
*Stay active in your question. If you asked this question and have not yet responded to the comments given by experts, please do so as soon as possible, even if it is just to say "I haven't been able to try that yet." If there are many comments, please address them all. If the suggestions given did not help, please give particulars such as error messages or other events that may have occurred. If the experts have asked questions, please respond to them to help them find a resolution to your issue. Please do not ignore them.
*If this issue remains unresolved, you may want to post a new, 0-point question here in this topic area with a link to this question in it to attract other experts.
*Award points for solutions. If one of the suggestions did work or if an expert put in a good effort toward resolving your issue, please award the points to that expert by clicking on the button above that expert's post that says "Accept comment as answer".
*If you have resolved this issue in another manner, please share it with us. We will refund your points and store the question in the database so others might get value from it.
*If you have given up on this issue, and prefer to delete it, you can request that, but the experts may choose to argue that they have worked hard to help you, and the moderator will make final determination if necessary.
*Most importantly, be fair in your grading. Please see guidelines for grading at
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
If you give any grade less than an A without stating why, the grade will be changed upon request by the expert.
Thanks very much for being fair to our experts.
-----Experts-----
*Do not provide further support to this question unless the Asker responds that it is not yet resolved and wishes to continue to get help.
*If you truly believe that your answer is the best one that has been provided, please state which comment you made that you believe to be correct. If no one argues your comment, you will be awarded the points.
*If you would like to *vote* for another expert's comment, feel free to do so. Likewise, feel free to argue (nicely) any points that other experts make. We want everyone's input to be considered. Unless it is really unavoidable, vote only for one expert's response--it is quite a bit of work to split points, and many people come and go from the site and never see the "Points for" questions.
*Please don't thank the moderator for granting points to you. This only causes more email for the moderator, and I can personally attest to literally 100s of emails a day. Your thoughts are no less appreciated! We are experts too.
*Finally, if you ever question a moderator's determination, don't hesitate to do so right in the question in question. If you feel you want to voice your comment privately, email me at amp@experts-exchange.com.
Your input is extremely valuable and very much appreciated.
thank you
amp
community support moderator
Sdeam:
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20130749
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20143486
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20161014
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20194767
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
1/21
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20130749
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20143486
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20161014
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20194767
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
1/21
sdeam: Please read #3 again!
thanks!
amp
community support moderator
thanks!
amp
community support moderator
Why are you trying to use just True/False when you have 3 conditions (All, Open, Closed)?
Ken