Criteria based on checkbox

P

Paul Washburn

Im trying to create a query that will return all results, but will apply
filters based on a checkbox. I found the code below from a previous
question, but this returns zero records if the box is unchecked.

Thanks for any help.


Code: 
[Forms]![FormRpt]![chkEdi] Or [Forms]![FormRpt]![chkEdi]=False
[code]
 
C

Crystal (strive4peace)

Hi Paul,

your criteria is checking for True and for False ... but
what if the checkbox is null?

this will look at the checkbox for a value and if it is
null, it will assume it is false:

nz([Forms]![FormRpt]![chkEdi],false)

to work, FormRpt must be open

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
:) have an awesome day :)
*
 
P

Paul Washburn

Thanks,

Not quite what im looking for though. I need the report to show all records
if nothing is checked. You example showed all records where the box was
unchecked.

I found that flipping my orignal code gave me the results i was looking for.
IE: Displayed all records when unchecked and only the pertaining records
when checked.

Code: 
([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

Crystal (strive4peace) said:
Hi Paul,

your criteria is checking for True and for False ... but
what if the checkbox is null?

this will look at the checkbox for a value and if it is
null, it will assume it is false:

nz([Forms]![FormRpt]![chkEdi],false)

to work, FormRpt must be open

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
:) have an awesome day :)
*


Paul said:
Im trying to create a query that will return all results, but will apply
filters based on a checkbox. I found the code below from a previous
question, but this returns zero records if the box is unchecked.

Thanks for any help.


Code: 
[Forms]![FormRpt]![chkEdi] Or [Forms]![FormRpt]![chkEdi]=False
[code][/QUOTE]
.
[/QUOTE]
Click to expand...
Click to expand...
 
P

Paul Washburn

Code: 
([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

Update:

The code above is working very sparadically. Ill run it once and get
nothing, run it again and get all results, run it a third time and get the
correct results. In the background the criteria is getting moved around,
added as new fields, and duplicated throughout the criteria.

Any help would be appreciated.

Thank you.

Paul Washburn said:
Thanks,

Not quite what im looking for though. I need the report to show all records
if nothing is checked. You example showed all records where the box was
unchecked.

I found that flipping my orignal code gave me the results i was looking for.
IE: Displayed all records when unchecked and only the pertaining records
when checked.

Code: 
([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

Crystal (strive4peace) said:
Hi Paul,

your criteria is checking for True and for False ... but
what if the checkbox is null?

this will look at the checkbox for a value and if it is
null, it will assume it is false:

nz([Forms]![FormRpt]![chkEdi],false)

to work, FormRpt must be open

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
:) have an awesome day :)
*


Paul said:
Im trying to create a query that will return all results, but will apply
filters based on a checkbox. I found the code below from a previous
question, but this returns zero records if the box is unchecked.

Thanks for any help.


Code: 
[Forms]![FormRpt]![chkEdi] Or [Forms]![FormRpt]![chkEdi]=False
[code][/QUOTE]
.
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Code: 
([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

Update:

The code above is working very sparadically. Ill run it once and get
nothing, run it again and get all results, run it a third time and get the
correct results. In the background the criteria is getting moved around,
added as new fields, and duplicated throughout the criteria.
Click to expand...

Please post the *complete* SQL view of the query.

As written it appears that this criterion will return the record if chkEDI is
False or if it is True - i.e. I'd expect it to return all records. I'm sure
that's not the case, so there's something else going on in the query!
 
P

Paul Washburn

I built the query using the query builder in access, so i the only additional
code is the default code from the builder.

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

The code i posted earlier was placed in the criteria section of the builder.
For the chkedi field.


Thanks

John W. Vinson said:
Code: 
([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

Update:

The code above is working very sparadically. Ill run it once and get
nothing, run it again and get all results, run it a third time and get the
correct results. In the background the criteria is getting moved around,
added as new fields, and duplicated throughout the criteria.
Click to expand...

Please post the *complete* SQL view of the query.

As written it appears that this criterion will return the record if chkEDI is
False or if it is True - i.e. I'd expect it to return all records. I'm sure
that's not the case, so there's something else going on in the query!
Click to expand...
 
J

John W. Vinson

I built the query using the query builder in access, so i the only additional
code is the default code from the builder.

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

The code i posted earlier was placed in the criteria section of the builder.
For the chkedi field.
Click to expand...

So you had

([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

as a criterion? That doesn't resemble what you posted as the SQL of the query.

What are form_name and chkEdi? I missed some of the start of this thread and
I'm not sure of the context here!
 
P

Paul Washburn

Yes, ([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi] was
originally entered as criteria. Didnt even know about the sql code in the
background until you asked about it. Started the form again and watched the
code in the background this time. The code started as

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE ((([Forms]![FormRpt]![chkEdi]=False) Or
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

I ran it once unchecked which produced no records; once checked which
produced no records; and then a third time unchecked which produced all
records and modified the code to this:

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE ((([Forms]![FormRpt]![chkEdi]=False) Or
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

Im still fairly new with sql but i was thinking a switch statement might
work something along the lines of (chkbox A, select statement, chkbox b,
select statement, chkbox A and chkbox B, select statement)

Thanks for the help

John W. Vinson said:
I built the query using the query builder in access, so i the only additional
code is the default code from the builder.

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

The code i posted earlier was placed in the criteria section of the builder.
For the chkedi field.
Click to expand...

So you had

([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

as a criterion? That doesn't resemble what you posted as the SQL of the query.

What are form_name and chkEdi? I missed some of the start of this thread and
I'm not sure of the context here!
Click to expand...
 
P

Paul Washburn

Sorry took a closer look at the code and realized
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi])); was present in both
sets of code.

form_name is a text field from the table that is not used in the query or
the form, just in the report that is geneted from the query.

chkedi is the name of the checkbox on the form. The field on the table is
called edi.

The idea is that the user generates the report based on their selections on
a form.
Ideally what i would like it to do is if the user selects none of the
checkboxes, then all records are displayed, otherwise records are filtered
based on the checkboxes the user checks.

Hope that clears it up. Thanks for the help

Paul Washburn said:
Yes, ([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi] was
originally entered as criteria. Didnt even know about the sql code in the
background until you asked about it. Started the form again and watched the
code in the background this time. The code started as

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE ((([Forms]![FormRpt]![chkEdi]=False) Or
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

I ran it once unchecked which produced no records; once checked which
produced no records; and then a third time unchecked which produced all
records and modified the code to this:

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE ((([Forms]![FormRpt]![chkEdi]=False) Or
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

Im still fairly new with sql but i was thinking a switch statement might
work something along the lines of (chkbox A, select statement, chkbox b,
select statement, chkbox A and chkbox B, select statement)

Thanks for the help

John W. Vinson said:
I built the query using the query builder in access, so i the only additional
code is the default code from the builder.

Code: 
SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi]));

The code i posted earlier was placed in the criteria section of the builder.
For the chkedi field.
Click to expand...

So you had

([Forms]![FormRpt]![chkEdi]=False) Or [Forms]![FormRpt]![chkEdi]

as a criterion? That doesn't resemble what you posted as the SQL of the query.

What are form_name and chkEdi? I missed some of the start of this thread and
I'm not sure of the context here!
Click to expand...
Click to expand...
 
J

John W. Vinson

Sorry took a closer look at the code and realized
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi])); was present in both
sets of code.

form_name is a text field from the table that is not used in the query or
the form, just in the report that is geneted from the query.

chkedi is the name of the checkbox on the form. The field on the table is
called edi.

The idea is that the user generates the report based on their selections on
a form.
Ideally what i would like it to do is if the user selects none of the
checkboxes, then all records are displayed, otherwise records are filtered
based on the checkboxes the user checks.
Click to expand...

You'll need to explain a bit more for this tired old brain.

The Text control form_name will not be equal to the value of a checkbox on a
form (which will be either -1 if checked or 0 if not).

Which records in the table do you want retrieved if chkEdi is checked?
What field contains the value to be searched?
What value does it contain if it is a "match"?
 
P

Paul Washburn

Im not sure why the data base is trying to compare anything to the form_name
field. I havent put it in any of the criteria so im not sure where that one
is coming from.

If the chkedi is check I want the report to return only records where edi is
set to yes. Edi is a yes/no field on the table.

That might be part of the issue if a check box checked/unchecked returns a
different number than a yes/no.

John W. Vinson said:
Sorry took a closer look at the code and realized
(bncforms_table.form_name)=[Forms]![FormRpt]![chkEdi])); was present in both
sets of code.

form_name is a text field from the table that is not used in the query or
the form, just in the report that is geneted from the query.

chkedi is the name of the checkbox on the form. The field on the table is
called edi.

The idea is that the user generates the report based on their selections on
a form.
Ideally what i would like it to do is if the user selects none of the
checkboxes, then all records are displayed, otherwise records are filtered
based on the checkboxes the user checks.
Click to expand...

You'll need to explain a bit more for this tired old brain.

The Text control form_name will not be equal to the value of a checkbox on a
form (which will be either -1 if checked or 0 if not).

Which records in the table do you want retrieved if chkEdi is checked?
What field contains the value to be searched?
What value does it contain if it is a "match"?
Click to expand...
 
J

John W. Vinson

Im not sure why the data base is trying to compare anything to the form_name
field. I havent put it in any of the criteria so im not sure where that one
is coming from.

If the chkedi is check I want the report to return only records where edi is
set to yes. Edi is a yes/no field on the table.
Click to expand...

That makes it ambiguous here: if the form checkbox chkEdi is False, do you
want to return records where the table field is False, or do you want to
return all records?
That might be part of the issue if a check box checked/unchecked returns a
different number than a yes/no.
Click to expand...

That's not a problem. A checkbox is either -1 if it's checked or 0 if not; the
same values are used for TRUE and FALSE in the table.

IF - and it's a big if! - you want to retrieve all records if chkEdi is False,
and only records where Edi is TRUE if it's checked, try

SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.Edi)=[Forms]![FormRpt]![chkEdi]))
OR [Forms]![FormRpt]![chkEdi] = False;

or, more simply but requires some mental contortions to get the Boolean logic,

SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE bncforms_table.Edi OR NOT [Forms]![FormRpt]![chkEdi];
 
P

Paul Washburn

Thanks, the code you provided worked with one exception. When the form first
opens if you click run without selecting any of the boxes no records are
returned. After youve clicked the check box once it works fine either way.
Does the checkbox have an initial value of null?



John W. Vinson said:
Im not sure why the data base is trying to compare anything to the form_name
field. I havent put it in any of the criteria so im not sure where that one
is coming from.

If the chkedi is check I want the report to return only records where edi is
set to yes. Edi is a yes/no field on the table.
Click to expand...

That makes it ambiguous here: if the form checkbox chkEdi is False, do you
want to return records where the table field is False, or do you want to
return all records?
That might be part of the issue if a check box checked/unchecked returns a
different number than a yes/no.
Click to expand...

That's not a problem. A checkbox is either -1 if it's checked or 0 if not; the
same values are used for TRUE and FALSE in the table.

IF - and it's a big if! - you want to retrieve all records if chkEdi is False,
and only records where Edi is TRUE if it's checked, try

SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE (((bncforms_table.Edi)=[Forms]![FormRpt]![chkEdi]))
OR [Forms]![FormRpt]![chkEdi] = False;

or, more simply but requires some mental contortions to get the Boolean logic,

SELECT bncforms_table.formnumber, bncforms_table.form_name,
bncforms_table.edi, bncforms_table.events, bncforms_table.[Weather Ntc],
bncforms_table.[No Enter]
FROM bncforms_table
WHERE bncforms_table.Edi OR NOT [Forms]![FormRpt]![chkEdi];

--

John W. Vinson JVinson *at* Wysard Of Info *dot* com

--

John W. Vinson [MVP]

.
Click to expand...
 
J

John W. Vinson

Thanks, the code you provided worked with one exception. When the form first
opens if you click run without selecting any of the boxes no records are
returned. After youve clicked the check box once it works fine either way.
Does the checkbox have an initial value of null?
Click to expand...

Probably. Set its DefaultValue property to True or False as appropriate.
 
P

Paul Washburn

Thanks for your help,

I had to set code the form load to set the check box to false, but after
that it works great.

My next issue is that Ive got 4 check boxes on the form and each one adds a
different filter. If both boxes are checked the filter works fine, but if
the first box is unchecked the filter reads false and returns all records
instead of just applying the 2nd filter.

Thanks
 
J

John W. Vinson

My next issue is that Ive got 4 check boxes on the form and each one adds a
different filter. If both boxes are checked the filter works fine, but if
the first box is unchecked the filter reads false and returns all records
instead of just applying the 2nd filter.
Click to expand...

Well... it would have helped a lot if you'ld said that.

If you want all four checkboxes you'll probably need to actually construct a
SQL string in code. I'm tied up with paying work at the moment, and leaving
for an extended road trip Saturday, so perhaps you should start a new thread
with a more complete description of what you want to accomplish. Sorry!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use TODAY() function as the criteria in a SUMIF formula 1
Using checkbox to edit criteria 3
If Statement with "*" in Query 2
How to modify and updte specific styles for specific paragraphs across the document 0
Checkbox & Textbox Used As Criteria For A Query 4
Help with IIF in Criteria 1
Using IN() with IIF in criteria 3
Using IN() with IIF in criteria 2

Top