Criteria based on checkbox

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" wrote:

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

 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:

 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" wrote:

On Tue, 1 Dec 2009 09:58:02 -0800, Paul Washburn
wrote:

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

  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.

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!
--

John W. Vinson [MVP]
.

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" wrote:

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

 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:

 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" wrote:

On Tue, 1 Dec 2009 09:58:02 -0800, Paul Washburn
wrote:

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

  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.

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!
--

John W. Vinson [MVP]
.

On Wed, 2 Dec 2009 12:14:02 -0800, Paul Washburn
wrote:

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.

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"?
--

John W. Vinson [MVP]

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" wrote:

On Wed, 2 Dec 2009 12:14:02 -0800, Paul Washburn
wrote:

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.

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"?
--

John W. Vinson [MVP]
.

On Thu, 3 Dec 2009 06:26:02 -0800, Paul Washburn
wrote:

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 makes it ambiguous he 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.

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]

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" wrote:

On Thu, 3 Dec 2009 06:26:02 -0800, Paul Washburn
wrote:

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 makes it ambiguous he 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.

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]

.

On Thu, 3 Dec 2009 11:00:01 -0800, Paul Washburn
wrote:

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?

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

John W. Vinson [MVP]

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

"John W. Vinson" wrote:

On Thu, 3 Dec 2009 11:00:01 -0800, Paul Washburn
wrote:

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?

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

John W. Vinson [MVP]
.

On Thu, 3 Dec 2009 14:14:09 -0800, Paul Washburn
wrote:

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.

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!
--

John W. Vinson [MVP]