Query using Checkbox State

[Guest]

I have a form which has several checkboxes on it. Each check box is used to
choose whether criteria such as 'live' or 'preferred' supplier information is
displayed on the form.

The default position i.e. 'true', for each of these swichboxes is to list
only the 'live' or 'preferred' suppliers. This works fine.

However, my problem is when the value is changed to 'false'. I expected to
see ALL of the records whether the supplier was 'preferred' or not but only
suppliers flagged as not preferred are displayed. How can I get all of the
records to be displayed if there the relevant checkbox is not selected as
'true'?

Hope this makes sense.

Terry

 

[Marshall Barton]

I have a form which has several checkboxes on it. Each check box is used to
choose whether criteria such as 'live' or 'preferred' supplier information is
displayed on the form.

The default position i.e. 'true', for each of these swichboxes is to list
only the 'live' or 'preferred' suppliers. This works fine.

However, my problem is when the value is changed to 'false'. I expected to
see ALL of the records whether the supplier was 'preferred' or not but only
suppliers flagged as not preferred are displayed. How can I get all of the
records to be displayed if there the relevant checkbox is not selected as
'true'?

Depends on how your query is put together. Post a
Copy/Paste of the query (or the VBA code that generates it)
so we can see what's going on.

 

[Jeff L]

Your query has criteria set on your checkbox, in which the value of the
checkbox is selected. You would need to eliminate the criteria for
when the value is false. I'm not sure as to how you are selecting your
records, so I can't tell you how to procede.

Hope that helps!

 

[John Vinson]

I have a form which has several checkboxes on it. Each check box is used to
choose whether criteria such as 'live' or 'preferred' supplier information is
displayed on the form.

The default position i.e. 'true', for each of these swichboxes is to list
only the 'live' or 'preferred' suppliers. This works fine.

However, my problem is when the value is changed to 'false'. I expected to
see ALL of the records whether the supplier was 'preferred' or not but only
suppliers flagged as not preferred are displayed. How can I get all of the
records to be displayed if there the relevant checkbox is not selected as
'true'?

Hope this makes sense.

Terry

Since we have no idea of the structure of your query or your table,
it's more than a bit difficult to answer. Please post the SQL view of
your current query. These *are* unbound checkboxes, I hope?

John W. Vinson[MVP]

 

[Guest]

I have a form which has several checkboxes on it. Each check box is used to
choose whether criteria such as 'live' or 'preferred' supplier information is
displayed on the form.

The default position i.e. 'true', for each of these swichboxes is to list
only the 'live' or 'preferred' suppliers. This works fine.

However, my problem is when the value is changed to 'false'. I expected to
see ALL of the records whether the supplier was 'preferred' or not but only
suppliers flagged as not preferred are displayed. How can I get all of the
records to be displayed if there the relevant checkbox is not selected as
'true'?

Hope this makes sense.

Terry

As suggested the following is a basic version of the query I'm using.

SELECT tbl_Courses.C_ID, tbl_Courses.C_Desc, tbl_TrainingCo.T_Co_Active
FROM tbl_TrainingCo RIGHT JOIN tbl_Courses ON tbl_TrainingCo.T_Co =
tbl_Courses.T_Co
GROUP BY tbl_Courses.C_ID, tbl_Courses.C_Desc, tbl_TrainingCo.T_Co_Active
HAVING ((tbl_TrainingCo.T_Co_Active)=[forms]![frm_UserOptions]![chkOpt6]);

Re Question regarding whether the check boxes are unbound. The checkboxes
ARE bound because the values are stored as preferences for each user.
Actually the results are shown on a different form as not on the same form as
I suggested in my question.

Thanks and hope this helps,

Terry

 

[John Vinson]

Re Question regarding whether the check boxes are unbound. The checkboxes
ARE bound because the values are stored as preferences for each user.

Since they are bound, be aware that if the user checks or unchecks the
checkbox, it will update that field in the table to which the form is
bound. This would let the user change their own preferences - perhaps
unintentionally!

In your current query:

SELECT tbl_Courses.C_ID, tbl_Courses.C_Desc,
tbl_TrainingCo.T_Co_Active FROM tbl_TrainingCo
RIGHT JOIN tbl_Courses ON tbl_TrainingCo.T_Co = tbl_Courses.T_Co
GROUP BY tbl_Courses.C_ID, tbl_Courses.C_Desc,
tbl_TrainingCo.T_Co_Active
HAVING
((tbl_TrainingCo.T_Co_Active)=[forms]![frm_UserOptions]![chkOpt6]);

you're specifically requesting that the value of T_Co_Active exactly
match the value in the checkbox chkOpt6. If the checkbox is TRUE you
will see all records where the field is TRUE; if the checkbox is FALSE
(unchecked), you will see all records where the field is FALSE. You're
getting exactly what you're asking for!

If instead you want to see only TRUE records if the checkbox is TRUE,
and all records (true or false) if it is FALSE, use:

SELECT tbl_Courses.C_ID, tbl_Courses.C_Desc,
tbl_TrainingCo.T_Co_Active
FROM tbl_TrainingCo
RIGHT JOIN tbl_Courses ON tbl_TrainingCo.T_Co = tbl_Courses.T_Co
GROUP BY tbl_Courses.C_ID, tbl_Courses.C_Desc,
tbl_TrainingCo.T_Co_Active
WHERE
tbl_TrainingCo.T_Co_Active = [forms]![frm_UserOptions]![chkOpt6]
OR [forms]![frm_UserOptions]![chkOpt6] = False;

Note that I changed your HAVING clause (which applies AFTER the
sorting and grouping) to WHERE (which applies beforehand). It's not
clear to me why (or whether) the query should be a Totals query, with
a GROUP BY - you're not using any totals operations. Might just a
simple SELECT query be better?


John W. Vinson[MVP]