Checkbox to a query

[gymcshoe via AccessMonster.com]

I have a form with a checkbox; I'm trying to build a query that grabs that
value from the checkbox (-1), and use it as a criteria. In the criteria, I
want to say: if the checkbox is checked, give me all records that do not have
a -1, otherwise give me all records.

So to clarify:
I have two tables. first one is "new table" and the second one is "archive
table".

I want to build a query, so that if the checkbox is checked, all records in
the "new table" that are NOT in the "archive table" will be shown. If the
checkbox is not checked, then I want all records to be shown.

the column I'm using to put the criteria in is Reviewed column in the Archive
table, which already has several -1's and is in number format

Any help will be much appreciated!

 

[John W. Vinson]

I have a form with a checkbox; I'm trying to build a query that grabs that
value from the checkbox (-1), and use it as a criteria. In the criteria, I
want to say: if the checkbox is checked, give me all records that do not have
a -1, otherwise give me all records.

Use a criterion of

NOT Forms!Yourformname!checkboxname OR Forms!yourformname!checkboxname = False

If the checkbox is checked (true), it will find all records where the field is
FALE; if it is unchecked (false), it will find all records (because the part
of the criterion after OR just examines the form control, and doesn't
reference any table field).

 

[gymcshoe via AccessMonster.com]

Use a criterion of

NOT Forms!Yourformname!checkboxname OR Forms!yourformname!checkboxname = False

If the checkbox is checked (true), it will find all records where the field is
FALE; if it is unchecked (false), it will find all records (because the part
of the criterion after OR just examines the form control, and doesn't
reference any table field).

Hi John, instead of finding all records where the field is False, is it
possible to find all fields where it is NULL?

 

[John W. Vinson]

Hi John, instead of finding all records where the field is False, is it
possible to find all fields where it is NULL?

Sure... what are the combinations? What records do you want to see if the
checkbox is True? What if it's False?

A criterion of

IS NULL

will find records where the table field is NULL. Combine that with a reference
to the checkbox using AND and you should have it.

 

[gymcshoe via AccessMonster.com]

Sure... what are the combinations? What records do you want to see if the
checkbox is True? What if it's False?

A criterion of

IS NULL

will find records where the table field is NULL. Combine that with a reference
to the checkbox using AND and you should have it.

Hi John, So if the checkbox is true, i want only those records in the New
table to show that are not in the archive table. if the checkbox is false, i
want to show all records.

with this:
(Not [Forms]![SearchForm1]![Check67]) Or [Forms]![SearchForm1]![Check67]
=False

when the checkbox is false, then i do get all records. But when the checkbox
is true, i get nothing when I should be getting the new records that are not
in the archive table. does that make sense? Thanks for yoru help

 

[John W. Vinson]

Hi John, So if the checkbox is true, i want only those records in the New
table to show that are not in the archive table. if the checkbox is false, i
want to show all records.

with this:
(Not [Forms]![SearchForm1]![Check67]) Or [Forms]![SearchForm1]![Check67]
=False

when the checkbox is false, then i do get all records. But when the checkbox
is true, i get nothing when I should be getting the new records that are not
in the archive table. does that make sense? Thanks for yoru help

Perhaps you could post the name and key fields of the main table and the
archive table and indicate how they are related.

 

[gymcshoe via AccessMonster.com]

Hi John, So if the checkbox is true, i want only those records in the New
table to show that are not in the archive table. if the checkbox is false, i

[quoted text clipped - 7 lines]

is true, i get nothing when I should be getting the new records that are not
in the archive table. does that make sense? Thanks for yoru help

Perhaps you could post the name and key fields of the main table and the
archive table and indicate how they are related.

Ok, I'll give it a try. In archive_table, I have 2 columns: domain and
reviewed. The reviewed column contains -1 (from a form that has a checkbox
that indicates if the domain has been reviewed. The new_table also has a
domain and reviewed column. I created a form with a checkbox to query the
new_table. The query behind the form I built joins the two tables using the
Domain column. So in the form, if the checkbox is checked, I want the query
to pull all data in the new_table that is not in the archive_table (so where
the reviewed column is null). If the checkbox is not checked, I want the
query to pull all records.

I hope that makes sense

 

[gymcshoe via AccessMonster.com]

can anyone help?

 

[John W. Vinson]

Ok, I'll give it a try. In archive_table, I have 2 columns: domain and
reviewed. The reviewed column contains -1 (from a form that has a checkbox
that indicates if the domain has been reviewed. The new_table also has a
domain and reviewed column. I created a form with a checkbox to query the
new_table. The query behind the form I built joins the two tables using the
Domain column. So in the form, if the checkbox is checked, I want the query
to pull all data in the new_table that is not in the archive_table (so where
the reviewed column is null). If the checkbox is not checked, I want the
query to pull all records.

I hope that makes sense

Sorry to be so long getting back to you.

A criterion of

WHERE (Forms!yourform!yourcheckbox = False)
OR (Forms!yourform!yourcheckbox = True AND Reviewed IS NULL)

should do it.

 

[gymcshoe via AccessMonster.com]

Ok, I'll give it a try. In archive_table, I have 2 columns: domain and
reviewed. The reviewed column contains -1 (from a form that has a checkbox

[quoted text clipped - 7 lines]

I hope that makes sense

Sorry to be so long getting back to you.

A criterion of

WHERE (Forms!yourform!yourcheckbox = False)
OR (Forms!yourform!yourcheckbox = True AND Reviewed IS NULL)

should do it.

Thanks for the reply John. Much appreciated. Unfortunately, this doesnt seem
to work. Do i put the criterion under the Reviewed column from the Archive
table in the query?

 

[John W. Vinson]

Ok, I'll give it a try. In archive_table, I have 2 columns: domain and
reviewed. The reviewed column contains -1 (from a form that has a checkbox

[quoted text clipped - 7 lines]

I hope that makes sense

Sorry to be so long getting back to you.

A criterion of

WHERE (Forms!yourform!yourcheckbox = False)
OR (Forms!yourform!yourcheckbox = True AND Reviewed IS NULL)

should do it.

Thanks for the reply John. Much appreciated. Unfortunately, this doesnt seem
to work. Do i put the criterion under the Reviewed column from the Archive
table in the query?

No. That's the WHERE clause of a entire SQL query. Perhaps you could take your
query (with some simple criterion such as True on the Reviewed field) and open
it in SQL view. You'll see a WHERE clause like

WHERE Reviewed = True

Edit the SQL to the above, using your actual form and control name.

 

[gymcshoe via AccessMonster.com]

Thanks John, I'll give that a try!