Not sure I can do this.

[Karen53]

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Ofer]

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

 

[Karen53]

Thank you!

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

Thank you!

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

Thank you!

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Ofer]

Hi
Are you trying to filter one filed with few check boxes, or each field for
each check box

Try and add "Or" to the filter
Where field = NZ(Forms![FormName]![FieldName],0) or field2 =
NZ(Forms![FormName]![FieldName2],0)

Mybe I need another explenation of how you expect the filter to work.

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

:

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Hi Ofer

For each Location I have equipment. Various business units use this
equipment and it varies by location and equipment as to who the business
units are. Each location/equipment can have more than one business unit
using it. So I can have multiple check boxes checked for a particular
location/equipment. Sometimes information on a specific type of equiipment
is requested with a particular business unit, sometimes just a business unit.
So, sometimes the combo box has a selection (equipment), sometimes it
doesn't. Sometimes a business unit is selected, sometimes it isn't.
Sometimes only one business unit is selected and sometimes more than one
needs to be selected.

Would "or" be my answer?

Thanks for your help.

Hi
Are you trying to filter one filed with few check boxes, or each field for
each check box

Try and add "Or" to the filter
Where field = NZ(Forms![FormName]![FieldName],0) or field2 =
NZ(Forms![FormName]![FieldName2],0)

Mybe I need another explenation of how you expect the filter to work.

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

:

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

:

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

I think the problem is that I am filtering each field to either true or
false. If it happens to be the business unit I selected and no other
business units are associated with that equipment I would get an accurate
return. Few have only one. ie "Sales" is selected to filter on so
"Production" and "Billing" are filtered to 0. Since they are actually true
on the record, they are not included. in the results.

Am I making sense?

Hi Ofer

For each Location I have equipment. Various business units use this
equipment and it varies by location and equipment as to who the business
units are. Each location/equipment can have more than one business unit
using it. So I can have multiple check boxes checked for a particular
location/equipment. Sometimes information on a specific type of equiipment
is requested with a particular business unit, sometimes just a business unit.
So, sometimes the combo box has a selection (equipment), sometimes it
doesn't. Sometimes a business unit is selected, sometimes it isn't.
Sometimes only one business unit is selected and sometimes more than one
needs to be selected.

Would "or" be my answer?

Thanks for your help.

Hi
Are you trying to filter one filed with few check boxes, or each field for
each check box

Try and add "Or" to the filter
Where field = NZ(Forms![FormName]![FieldName],0) or field2 =
NZ(Forms![FormName]![FieldName2],0)

Mybe I need another explenation of how you expect the filter to work.

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

:

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

:

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

:

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Ofer]

In that case did you conside using a list box with multiple selection for the
items?
It will be easier to impliment, and more correct.
With the check box method you need to create another check box every time
you create a new item, and then you will need to update your code.

I think the problem is that I am filtering each field to either true or
false. If it happens to be the business unit I selected and no other
business units are associated with that equipment I would get an accurate
return. Few have only one. ie "Sales" is selected to filter on so
"Production" and "Billing" are filtered to 0. Since they are actually true
on the record, they are not included. in the results.

Am I making sense?

Hi Ofer

For each Location I have equipment. Various business units use this
equipment and it varies by location and equipment as to who the business
units are. Each location/equipment can have more than one business unit
using it. So I can have multiple check boxes checked for a particular
location/equipment. Sometimes information on a specific type of equiipment
is requested with a particular business unit, sometimes just a business unit.
So, sometimes the combo box has a selection (equipment), sometimes it
doesn't. Sometimes a business unit is selected, sometimes it isn't.
Sometimes only one business unit is selected and sometimes more than one
needs to be selected.

Would "or" be my answer?

Thanks for your help.

Hi
Are you trying to filter one filed with few check boxes, or each field for
each check box

Try and add "Or" to the filter
Where field = NZ(Forms![FormName]![FieldName],0) or field2 =
NZ(Forms![FormName]![FieldName2],0)

Mybe I need another explenation of how you expect the filter to work.


:

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

:

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

:

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

:

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[Karen53]

Ofer,

The record needs to hold multiple business units for each record and I need
to be able to filter on each. A field only holds one value from a list box.
Am I understinding a list box correctly?

In that case did you conside using a list box with multiple selection for the
items?
It will be easier to impliment, and more correct.
With the check box method you need to create another check box every time
you create a new item, and then you will need to update your code.

I think the problem is that I am filtering each field to either true or
false. If it happens to be the business unit I selected and no other
business units are associated with that equipment I would get an accurate
return. Few have only one. ie "Sales" is selected to filter on so
"Production" and "Billing" are filtered to 0. Since they are actually true
on the record, they are not included. in the results.

Am I making sense?

Hi Ofer

For each Location I have equipment. Various business units use this
equipment and it varies by location and equipment as to who the business
units are. Each location/equipment can have more than one business unit
using it. So I can have multiple check boxes checked for a particular
location/equipment. Sometimes information on a specific type of equiipment
is requested with a particular business unit, sometimes just a business unit.
So, sometimes the combo box has a selection (equipment), sometimes it
doesn't. Sometimes a business unit is selected, sometimes it isn't.
Sometimes only one business unit is selected and sometimes more than one
needs to be selected.

Would "or" be my answer?

Thanks for your help.

:

Hi
Are you trying to filter one filed with few check boxes, or each field for
each check box

Try and add "Or" to the filter
Where field = NZ(Forms![FormName]![FieldName],0) or field2 =
NZ(Forms![FormName]![FieldName2],0)

Mybe I need another explenation of how you expect the filter to work.


:

Hi,

Unfortunately, there must be more than one checked for some of the records.
Is there a way around this?

Thanks

:

Hi,

Ok, I know why they are inaccurate. Each record can have more than one
check box True and I am only filtering to the value of one. So some of them
are true and the filter is looking for false.

:

Hi,

At first
Where field = NZ(Forms![FormName]![FieldName],0)
seemed to work but upon further checking I am not getting accurate results.
Any suggestions?

:

Hi,

I am still having trouble.

Where field = NZ(Forms![FormName]![FieldName],0)
This works beautifully as long a check box is selected.

If I add:
Where field Like NZ(Forms![FormName]![FieldName],"*")
I get nothing (check box not selected).

If I put
Where field = NZ(Forms![FormName]![FieldName],0)
and a check box is not selected I only get 7 records when there should have
been 66. Am I missing something? Sometimes I run this selecting a check box
and sometimes without.

Thanks for your help.

:

Thank you!

:

I check box can have two values , True and False, so if you didn't check it
you need to filter on false.

In that case try and use
Where field = NZ(Forms![FormName]![FieldName],0)

But if you want to select all the values if you didnt check the box, try and
use
Where field Like NZ(Forms![FormName]![FieldName],"*")

--
In God We Trust - Everything Else We Test


:

Hi,

What I would like is a form with a combo box to filter a query. That I have
so far. But then I would like a series of check boxes. If one check box is
checked it filters for that check box in addition to my combo box. Where I
am getting hung up is each check box on the form has a matching boolean field
in the record. I have tried setting up the query for each check box to link
to the check box on the form. The trouble seems to be it can't be blank. I
get nothing when I query. So, I think I need to link only the check box I am
filtering to, which is variable. Can I place the link through VB onto the
query from the form based on which check box is selected?

Thanks

 

[John Vinson]

Ofer,

The record needs to hold multiple business units for each record and I need
to be able to filter on each. A field only holds one value from a list box.
Am I understinding a list box correctly?

You need ANOTHER TABLE.

A Field cannot hold multiple values, of course. You have a one-to-many
or many-to-many relationship; consider using a Subform rather than a
listbox for data entry. A multiselect listbox cannot directly store
data in a table - you need to write VBA code to loop through the
selected items and store them. Doable but a subform is much simpler!

John W. Vinson[MVP]

 

[Karen53]

Hi John,

That is why I used check boxes but am having trouble filtering by them. See
my earlier postings. I take it then, there isn't a way to filter these
check boxes?

 

[John Vinson]

Hi John,

That is why I used check boxes but am having trouble filtering by them. See
my earlier postings. I take it then, there isn't a way to filter these
check boxes?

A checkbox IS NOT DATA.

A checkbox is a display tool, just as a Combo Box or a Textbox are
display tools.

I presume - you haven't explicitly said - that you have a
(non-normalized, in need of redesign per my suggestions :-{( ) table
with Yes/No fields ("checkboxes" in table datasheet view) and that you
want to select records based on the values in these checkboxes. Of
course it is possible to query on the value of these yes/no fields!
Access wouldn't be much use if you couldn't use it for searching!

But please... give us a break.

What is the structure of your table?
What is the SQL view of the query which is returning too few records?
Please open it in SQL view and copy and paste the text to a message
here.

You might have posted some of this information earlier, but I haven't
taken the time to fire up Google Groups and search for it - maybe a
fresh start would be better anyway!

John W. Vinson[MVP]

 

[Karen53]

Hi John,

I assumed you could see the whole thread. Each check box is a business
unit. Each record can have more than one business unit associated to it. I
filter for the value of one business unit. But I think the problem is that
some of the others are true also.

Here is the SQ. Balboa begins the check boxes.

SELECT qry_postmortems_Date.Vendor, qry_postmortems_Date.OutageDate,
qry_postmortems_Date.Location, qry_postmortems_Date.IssueCause,
qry_postmortems_Date.Issue_Platform, qry_postmortems_Date.BriefDescription,
qry_postmortems_Date.Balboa, qry_postmortems_Date.CapitalMkt_SecondaryMkt,
qry_postmortems_Date.CIS, qry_postmortems_Date.CLD,
qry_postmortems_Date.CMD_Corp, qry_postmortems_Date.CMD_Remote,
qry_postmortems_Date.FSL_Corp, qry_postmortems_Date.FSL_Remote,
qry_postmortems_Date.HR, qry_postmortems_Date.Landsafe,
qry_postmortems_Date.LoanAdmin, qry_postmortems_Date.ProductionTech,
qry_postmortems_Date.TBank, qry_postmortems_Date.Wholesale_Corp,
qry_postmortems_Date.Wholesale_Remote
FROM qry_postmortems_Date
WHERE (((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*") AND
((qry_postmortems_Date.Balboa)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Balboa],0))
AND
((qry_postmortems_Date.CapitalMkt_SecondaryMkt)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_CSC],0))
AND
((qry_postmortems_Date.CIS)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_CIS],0))
AND
((qry_postmortems_Date.CLD)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_CLD],0))
AND
((qry_postmortems_Date.CMD_Corp)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_CMD_Corp],0))
AND
((qry_postmortems_Date.CMD_Remote)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_CMD_Remote],0))
AND
((qry_postmortems_Date.FSL_Corp)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_FSL_Corp],0))
AND
((qry_postmortems_Date.FSL_Remote)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_FSL_Remote],0))
AND
((qry_postmortems_Date.HR)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_HR],0))
AND
((qry_postmortems_Date.Landsafe)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Landsafe],0))
AND
((qry_postmortems_Date.LoanAdmin)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_LoanAdmin],0))
AND
((qry_postmortems_Date.ProductionTech)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_PT],0))
AND
((qry_postmortems_Date.TBank)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_TBank],0))
AND
((qry_postmortems_Date.Wholesale_Corp)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Wholesale_Corp],0))
AND
((qry_postmortems_Date.Wholesale_Remote)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Wholesale_Remote],0)))
ORDER BY qry_postmortems_Date.OutageDate;


Thanks,

 

[John Vinson]

WHERE (((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*") AND
((qry_postmortems_Date.Balboa)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Balboa],0))
AND

The problem is that OR.

If either of the first two criteria are true, the record will be
retrieved REGARDLESS of the values in the checkboxes.

Put an extra left parenthesis after WHERE, and an extra right
parenthesis before the first AND.

And...

****** NORMALIZE YOUR TABLES ********

If you think *this* query is difficult, they'll get a LOT worse (if
you want to do, say, a date range). And the difficulty is all
*unnecessary* - the syntax

WHERE ((((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*"))
AND (
(Forms!frmPOP_NeilsTrendReports![chk_Balboa] AND BusinessUnit =
"Balboa")
OR
(Forms!frmPOP_NeilsTrendReports![chk_CIS] AND BusinessUnit = "CIS")
OR
( <etc etc> )

will let you store the data correctly - without multiple yes/no fields
and without storing data in fieldnames - and still allow the user to
query by selecting checkboxes.


John W. Vinson[MVP]

 

[Karen53]

Hi John,

Ok, here is my code. When I saved the query, Access added parenthesis'. I
keep trying to add the extra ( after Where and an extra ) before and but
access gives me an syntax error. When I run this the filter is not 100%. Am
I missing something?


WHERE (((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*") AND
((qry_postmortems_Date.Balboa)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_Balboa])<>False)) OR
(((qry_postmortems_Date.CapitalMkt_SecondaryMkt)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_CSC])<>False)) OR
(((qry_postmortems_Date.CIS)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_CIS])<>False)) OR
(((qry_postmortems_Date.CLD)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_CLD])<>False)) OR
(((qry_postmortems_Date.CMD_Corp)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_CMD_Corp])<>False)) OR
(((qry_postmortems_Date.CMD_Remote)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_CMD_Remote])<>False)) OR
(((qry_postmortems_Date.FSL_Corp)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_FSL_Corp])<>False)) OR
(((qry_postmortems_Date.FSL_Remote)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_FSL_Remote])<>False)) OR
(((qry_postmortems_Date.HR)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_HR])<>False)) OR
(((qry_postmortems_Date.Landsafe)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_Landsafe])<>False)) OR
(((qry_postmortems_Date.LoanAdmin)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_LoanAdmin])<>False)) OR
(((qry_postmortems_Date.ProductionTech)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_PT])<>False)) OR
(((qry_postmortems_Date.TBank)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_TBank])<>False)) OR
(((qry_postmortems_Date.Wholesale_Corp)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_Wholesale_Corp])<>False)) OR
(((qry_postmortems_Date.Wholesale_Remote)=True) AND
(([Forms]![frmPOP_NeilsTrendReports]![chk_Wholesale_Remote])<>False))
ORDER BY qry_postmortems_Date.OutageDate;


Thanks for help,

WHERE (((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*") AND
((qry_postmortems_Date.Balboa)=NZ([Forms]![frmPOP_NeilsTrendReports]![chk_Balboa],0))
AND

The problem is that OR.

If either of the first two criteria are true, the record will be
retrieved REGARDLESS of the values in the checkboxes.

Put an extra left parenthesis after WHERE, and an extra right
parenthesis before the first AND.

And...

****** NORMALIZE YOUR TABLES ********

If you think *this* query is difficult, they'll get a LOT worse (if
you want to do, say, a date range). And the difficulty is all
*unnecessary* - the syntax

WHERE ((((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*")) OR
(((qry_postmortems_Date.Issue_Platform) Like "*" &
[Forms]![frmPOP_NeilsTrendReports]![txt_NeilsTrendRpt] & "*"))
AND (
(Forms!frmPOP_NeilsTrendReports![chk_Balboa] AND BusinessUnit =
"Balboa")
OR
(Forms!frmPOP_NeilsTrendReports![chk_CIS] AND BusinessUnit = "CIS")
OR
( <etc etc> )

will let you store the data correctly - without multiple yes/no fields
and without storing data in fieldnames - and still allow the user to
query by selecting checkboxes.


John W. Vinson[MVP]