or function within IIf statement in query criteria (1 Viewer)

dlambert

Member
Local time
Today, 17:27
Joined
Apr 16, 2020
Messages
42
Hello all,
I am in the process of building a database system to manage the work hours allocation in the company i work form.

Within a query i have a field called Invoiced, this is a Yes/No value

In the query, the criteria:
Yes or No
works to display BOTH the invoiced and not invoiced entries
Like "*"
also works the same.

In a separate form i have one checkbox for ShowInvoiced, and another for ShowNotInvoiced
In the query i have set up a simple nested iif statement that should show me depending on the status of the tickboxes either the Invoices, NotInvoiced or Both.

However, when i try to use the IIf statement, the 'Yes or No' and 'Like "*"' values do not work as they did when they were typed on their own...


Here are some simplified test IIf statement that do NOT work as expected in the query criteria(though i have tested the statement itself works OK as if i change the values to either Yes or No it behaves accordingly?
IIf([forms]![frmHoursEnquiry]![InvoicedCheckBox]=Yes,Yes or No,No)
IIf([forms]![frmHoursEnquiry]![InvoicedCheckBox]=Yes,Like "*",No)
I tried adding brackets but that did not make a difference...

Would anyone have any suggestions on how i can get this to work?

Thanks in advance
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:27
Joined
Feb 19, 2013
Messages
16,553
you cannot incorporate your criteria in this way. Yes/no fields are numeric, 0=false/no, -1=true/yes

what are you actually trying to achieve with your criteria - what field is it applied to? what are the possible field values? and what are the outcomes required
 

dlambert

Member
Local time
Today, 17:27
Joined
Apr 16, 2020
Messages
42
Thankyou for you quick response CJ_London,
What i want to achieve a way to 'filter' a query result based on the Yes/No Invoiced field. But i want to be able to use a form to show Invoiced, NotInvoiced or Both. It is the Both that is causing be problems....
I don't mind doing it using 2 checkboxes, or 3 radio buttons or similar.... as long as it can be controlled from a form.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:27
Joined
Feb 19, 2013
Messages
16,553
suggest use an option box - Yes, No and All - set yes=1, no=2, all=3

in your criteria the formula would be

IIf([forms]![frmHoursEnquiry]![InvoicedOptionBox] =3,[Invoiced],[forms]![frmHoursEnquiry]![InvoicedOptionBox]-2)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:27
Joined
May 7, 2009
Messages
19,169
use OptionGroup with 3 options (invoiced, not invoiced, both).

use this criteria on your query:

[invoiced] = choose(forms]![frmHoursEnquiry]!optionGrpName, -1, 0, [invoiced])
 

dlambert

Member
Local time
Today, 17:27
Joined
Apr 16, 2020
Messages
42
Thankyou both CJ_London and arnelgp,
Problem resolved.
The key thing was using [Invoiced] in the square brackets within the statement.
I put that into my original IIf statements and it all works as i originally expected. But thanks to you i also now know how to use the OptionGroup, i am sure this will come in handy some time.
Thanks a lot!!
 

Users who are viewing this thread

Top Bottom