Access 2007 - Conditional Criteria in Query

[sao123]

So, im still developing a VBA-less database, and this time i have encountered a new situation, I dont know how to solve.

I have a field in a table which is simply a yes/no.

One of the reports we typcially run, I need 2 version of the report.

1 version of the report only includes records with a yes, the other includes all records. (consider this a sort of pseudo filter via query)

i have a combo control on my form designed to tell the report to query on only yes's, or on all values.
In the query my query criteria is:

IIF([Forms]![Switchboard]![Limit] = 1, 1, 1 or 2)
this translates into

IIF([Forms]![Switchboard]![Limit] = yes, yes, yes or no)
[these values are in a related table...]
1 yes
2 no


but this doesnt work. specifically the "1 or 2" part in the false statement, is the part which isnt working.
i have tried "1 or 2", "*", and a few others.
is there a "all values" I can use in my IIF statement?

 

[PhatoseAlpha]

There is not a Boolean ALL, AFAIK.

Why not replace your higher logic? An OR is appropriate for what you're intending to do. Where (NameOfTheField OR (NOT [Forms]![Switchboard]![Limit])) should do the trick. If the limit checkbox isn't checked, the second portion is true no matter what the Field value is and the field will always be returned. If it is checked, then then OR will only be true if the field is true, thus behaving like you want.

 

[sao123]

There is not a Boolean ALL, AFAIK.

Why not replace your higher logic? An OR is appropriate for what you're intending to do. Where (NameOfTheField OR (NOT [Forms]![Switchboard]![Limit])) should do the trick. If the limit checkbox isn't checked, the second portion is true no matter what the Field value is and the field will always be returned. If it is checked, then then OR will only be true if the field is true, thus behaving like you want.

I had thought of this, but this would require major table redesign.

the yes/no is stored in a table with ID's as integers 1 = yes, 2 = no and a full relationship. This was done for other statistical reasons throughout the database. I cant or 1's and 2's, and get the same result, can I?

 

[EagleKeeper]

test for < 2 being Yes
test for < 3 for being ALL

all you need to do is enter the proper parameter when prompted

 

[sao123]

thx, appears to be working now.

 

[EagleKeeper]

What did you impliment?

 

[sao123]

What did you impliment?

<= value of the combobox control.

dont know why i was trying to OR it piecewise, when <= was such a simple solution.

 

[shortylickens]

Damn, and I was just going over this in IT class last week.
But now I cant remember. Lemme dig into my books today and see what I can see.

 

[AstroNut]

I had to solve a Yes/No parameter problem for a field called VIPEvent. I finally remembered Access telling me earlier that -1 was Yes and 0 was No. However, I really didn't want users typing those values in, so I added a non-displaying column to my query:

IIf([VIPEvent]=-1,"Yes","No")

This converts the Yes/No (-1, 0) values to user-friendly Yes and No values. Then I added the following prompt:

Like [Only Show VIP Events?] & '*'

which uses the nicer text comparison so that leaving it blank returns everything, if that's what you want. Hope this helps.