how do i select criteria based on a value in a check box?

[Steven.Foy]

Hi wonder if anyone can help.....

Am trying to filter a query based on whether a check box is selected or not. I've included the criteria row from my Query....

IIf([Forms]![frmfunctionlocation]![rbexcludeBIF]=-1,Not Like "BIF",Not "")

I read this as... if the check box(rbexcludeBIF) has been clicked(=-1) i want to not include the string BIF in my query. Else i want to include all fields(i.e not blank)

I think this is nearly right...but at the moment the query returns a blank screen!

Any ideas?

 

[EMP]

I read this as... if the check box(rbexcludeBIF) has been clicked(=-1) i want to not include the string BIF in my query. Else i want to include all fields(i.e not blank)

What is BIF? Is it a string (or part of a string) in a field? Or is it a field name?

 

[Steven.Foy]

BIF is a string within a field. If my terminology is correct!. "BIF" is one of a number of 3 digit variables which occur uniquely within one column in my database query. Hope that makes a bit of sense!

 

[Jon K]

Put this in a new column in the query grid (replacing with the correct FieldName):-

Field: IIf([Forms]![frmfunctionlocation]![rbexcludeBIF], [FieldName]<>"BIF", [FieldName] Is Not Null)

Show: uncheck

Criteria: <>False

.

 

[Steven.Foy]

Thankyou very much...problem solved. I had been trying to execute the If statement as a criteria rather than in a field.

Can i ask what the <>FALSE statement does in the criteria row? i understand the If statement.

Most importantly...it works!

 

[mcadle]

another way

I also do this and use this format. In the actual code for check box on click:
docmd.applyfilter , ,"field=whatever"

if you have multiple checkboxes then have it set the other checkboxes to false.

 

[Jon K]

Can i ask what the <>FALSE statement does in the criteria row?

The <>FALSE in the criteria cell tells Access to use the IIF expression in the field cell above as a criterion. It's one of Access' query grid defaults.

If you switch the query to SQL View, you will see that Access has put the IIF expression in the Where Clause, that is, the criteria of the SQL statement. If you directly type the IIF expression in the Where Clause in SQL View, you don't need to type <>False.

.

 

[Pat Hartman]

Your original intention was to have the IIf() return a string. This resulted in a where clause that evaluated to:

Where SomeField = "Like 'BIF'"

Although your intention was to have a where clause that looked like:

Where SomeField Like "*BIF*"

Jon changed the IIf() so that it returned true or false. Then the selection criteria would be:

Where Somefield <> FALSE -- where the true false was calculated by the IIf()

The criteria could just as easily be written as:
Where Somefield = TRUE

 

[DLB]

I am just curious.

The <>FALSE in the criteria cell ......... It's one of Access' query grid defaults.

The criteria could just as easily be written as:
Where Somefield = TRUE

How do we know <>False in the criteria is one of Access's query grid defaults? Not =True?

 

[Pat Hartman]

<> FALSE isn't a default. It is the criteria that is generated by Jet in certain cases.
null <> false results in False
true <> false results in True
false <> false results in False
and
null = True results in False
true = true results in True
false = true results in False

So, I'm not sure why Access would generate a negative condition rather than a positive one. Could be for the same reason that the wizards generate Access 95 DoMenuItem's rather than proper VBA.

 

[Jon K]

DLB,

You can easily test it for yourself.

Using Steven's example, you can type this query in the SQL View of a new query:-
Select *
From [TableName]
Where IIf([Forms]![frmfunctionlocation]![rbexcludeBIF], [FieldName]<>"BIF", [FieldName] Is Not Null)

without adding <>False or =True at the end of the Where Clause, and save the query.


Then re-open the query in Design and switch to Design View. You can see that Access has put <>False in the criteria cell for you. Access hasn't put =True there for you!

That's why I said in my previous post: <>False is one of Access's query grid defaults.
(Note I emphasised the "query grid". Perhaps Pat and I was referring to different things.)


You can change <>False to =True (Access will strip the = sign) in the criteria cell and the query works just as fine. I used <>False just to make it consistent with the way Access does it.


I think what usually surprise people is when I tell them to move the IIF criteria from the criteria cell to the field cell. In their minds, it is contrary to what they usually do. In fact, I don't just tell them to move the IIF expression to the field cell. I tell them to insert the FieldName in the IIF expression, too. Now you just need some way to tell Access to use the IIF expression as the criteria. Hence, you have the <>False default.


Hope this helps.

.

 

[DLB]

Jon,

Thanks for the clear explanation. Now I understand the query Design view and SQL view much better.

 

[Keith27]

Jon K,

I program a lot and have quite some experience in using IIF in Access and other programs. When I saw your solution:-

Field: IIf([Forms]![frmfunctionlocation]![rbexcludeBIF], [FieldName]<>"BIF", [FieldName] Is Not Null)

Show: uncheck

Criteria: <>False


immediately I thought this should also work:-
-----------------------
Field: [FieldName]

Show: check

Criteria: IIf([Forms]![frmfunctionlocation]![rbexcludeBIF], <>"BIF", Is Not Null)
------------------------
But of course it doesn't, or you would have posted it, wouldn't you? No errors, it just returns nothing.


But by logical deduction it should work! Do you know why it doesn't?

Thanks in advance.
Keith

 

[Steven.Foy]

its strange you should say that Keith, as that was my take on things too. I had expected a similiar approach to the IF statment in the criteria row to have the same effect.

I guess there must be a logical answer as to why it doesn't work though!

 

[Jon K]

The problem lies in the inner working of the query Design View.

When we put a criteria that does not begin with >, >=, <, <=, <>, LIKE, IN, BETWEEN or IS in a criteria cell, Access will:-

1) add an invisible = sign in front of the criteria (the = sign is visible in SQL view), and
2) insert the field name in front of each operator in the criteria if one is not there​

when the query is saved.

It was these extras that caused all the trouble!
.

 

[Keith27]

Jon K,

Thanks. You seem to know so much! These have never been covered in the Access books that I have. I wonder where you learned all these!

Thanks again.
Keith