Multiple check boxes to select records

[WalterInOz]

I have a database which contains hundreds of publications, all listed on the main (continuous) form.
To make it a little easier for the users I thought I'd let them select the main 'areas of interest' by ticking one or more radio buttons (7).Tthe result should be that only those records are shown that have been tagged for the selected area(s).

There are a few issues that I cannot get my head around and I'd appreciate advice. The main problem (I think) is that I don't know what I should write in the 'where' part of my query. I currently have this:

Is Not Null And [Forms]![frmPubInSelectedArea]![OptionQu]

Seven times, for 7 buttons, on different lines, and yes, with different names for the Option Buttons.

This works OK the first time I select a button. After deselecting the same button the query still yields the same records: the value of the button goes from Null, to -1 to 0.

I think I understand what happens, but I have not been able to find how I should refer to the '0-state' in the query. It's clearly not the same as Null, but what is it? Or is there a better way to achieve my goal then buttons?

thanks for your advice

 

[neileg]

Zero is the value for false and -1 is the value for true. If you want to treat null and 0 the same then use Nz() to turn the null to 0 and test to see if the value is 0. Could look like this:
NZ(MyField,0)<>0

 

[WalterInOz]

Thank you for your help Neil. unfortunately you go a little too fast for me.
I have found some background reading on the Nz function and what it does but I am still not able to put 1 and 1 together.

Did you understand that I don't want the query to return the records with 0 or Null, only the records that have a -1? If you did, I don't understand what to do with your suggestion. I don't see how that fits in the query. Could you please elaborate?

Thank you

 

[neileg]

Depnds what you want to do, really. A yes/no field can have 3 values, null, 0 or -1. As you know 0 means false and -1 means true. What do you want to treat the null as, false or not yet answered?

NZ() allows you to look at a field. If the field is null, it will return whatever value you specify in the Nz() statement (which is 0 by default) or will return the non-null value.

So on a yes/no field Nz(MyField,0) returns:
-1 if the value is -1
0 if the value is 0
0 if the value is null

 

[b_c]

here's my dilemma (which is similar to walters):

i've got 13 different yes/no check box fields in various tables in my database. i'm running a querry to identify these fields per the "patient" ...these check boxes signifying different diagnosis and such.

i have a form with 13 unbound checkboxes and i'm trying to use the input for which fields to search for in the query.

from what i've tried i either get all or none. what am i doing wrong?

...let me know if you need more info.


cheers,


b_c

 

[b_c]

here's my dilemma (which is similar to walters):

i've got 13 different yes/no check box fields in various tables in my database. i'm running a querry to identify these fields per the "patient" ...these check boxes signifying different diagnosis and such.

i have a form with 13 unbound checkboxes and i'm trying to use the input for which fields to search for in the query.

from what i've tried i either get all or none. what am i doing wrong?

...let me know if you need more info.


cheers,


b_c

would a query of 13 queries work? ...i think it would, but to me, there's got to be a "cleaner" way to do what i want to do.


b_c

 

[neileg]

Sounds like a fundamental promblem with your table design. Without knowing how you've built your database I cant help.

 

[wilpeter]

BC: I'm guessing that your form is a selection form only, hence a parameter that feeds the query. Since the query can hold 9 "or" criteria only, I'd suggest splitting the task into stages: Query 1 uses the equivalent Tables and their Y/N corresponding fields for the first 9 boxes; Query 2 uses the same for the remaining 4 boxes. Both would be Append Queries. You'll need a Table that contains the required fields for the report/output. The selection form can have a command/macro that runs sequentially a reset of this Table to 0 records; the two Append queries; and a display either as a Datasheet or Report. Does this help at all?