Parameter query with multiple check boxes

[Aviator]

I am creating a database to manage past jobs done for quoting possible new
jobs. My database has fields for basic info about the customer, than 25 check
box fields with different categories of work completed. One job(record) will
have multiple boxes cecked depending on the work completed. I want to run a
parameter query through a form with those 25 check boxes. I want it so that
if I select one of those boxes, all records that have that field selected,
will be shown. If multiple boxes on the form are selected, any record that
has just one of those fields selected, they will be shown. Help would be
greatly appreciated. I have the form created, but have been unable to return
results. No errors messages.

 

[NetworkTrade]

check that your query is working in pure query mode - leaving the Form aside
for the moment

in query mode

at this point one can't be sure if the issue is your underlying query - or
between the form and the query

 

[Aviator]

In pure query mode it is not returning any results either. I assume I am
answering the parameter dialog boxes correctly. When the 25 boxes pop up, I
enter a 0 for no and a 1 for yes. No results are returned. I have a combo box
as well that selects aircraft type, (it is for aircraft work orders) that is
working correctly.

 

[NetworkTrade]

since your basic query is not returning anything you need a sanity check:

in query mode

anything put in the Criteria field will act as a filter

so as a sanity check if I were you I would run a query with nothing in any
criteria field just to see it return all records and all fields of the
table(s)

once this is accomplished

and one value in one criteria value....and note return values....experiment
around until you understand the behavior of your query...

off the cuff - can't remember if y/n field is 1,0 or true,false...you might
try both to see...

 

[Aviator]

I cleared all the criteria boxes and hand typed all 25, I had copied,
pasted, and edited them for speed last time. Perhaps that was a problem. I
can now pull records with my form. Problem is for example, I select fields
2,5,12,17,25 on my parameter form and it will only pull results that meet all
those criteria. I want it to pull all records that have all or just one of
those criteria.

 

[Aviator]

Another issue. if I enter the criteria code, ([Form]![Formname]![Fieldname])
in more than 3 fields on the query the query does not return any results.

 

[NetworkTrade]

yeah you are building an AND statement instead of an OR statement.....off the
cuff I can't think of a simple way to do an Or statement in the mode that you
have set up....

 

[Duane Hookom]

One of your issues is that your table is not normalized. 25 check box fields
is not the ideal way to store your data.

However, I expect you want to display your results in a form or report. If
so, this question is much easier to answer.

 

[Aviator]

I tryied doing this database with a combo box instead of check boxes and it
was ineffiecient. This way a user can simple check multiple categories of
work that were performed on a job instead of creating a different record for
each type of work performed. Example, on an aircraft, work done on the seats,
carpet, windows. The boxes seats, carpet, windows would be selected so when a
user goes to quote a new job they can look back at past work completed in
those categories and use the time and cost from those past jobs. The
query/report will display the work order number so that user can simple look
in those work orders and find the info they need.

 

[Duane Hookom]

You don't understand my comments on normalization. You should not have
fields like "seats", "carpet",... These are data values and should not be
field names. How would you handle work done on a new item such as "Arm
Rests"? You shouldn't have to add fields and controls to maintain a system.

I expected to get an answer from you like "Yes, the results of the query are
displayed in a report". Do your users see the query or do they see the
results of the query in a form or report?

 

[Aviator]

I have the aircraft broken into 25 items(check boxes). What ever section is
worked on is what is selected. There will be no more added. A catch all
"Other" is included. When a user needs to qoute work to be done on a arm
rest, they would select the check box for seats in the parameter query form
that I am trying to get to work. If they need to qoute a job that will work
on arm rests and a TV installation for a lear jet, they would select lear jet
from the list box of aircraft and the seat check box, and entertainment check
box. This, (as I hope) would return the work orders that were from a lear jet
that had seat work and/or entertainment work. This would give the user a
starting point to determine what they should qoute it for.

 

[NetworkTrade]

Duane is one of the best support person that tracks on this forum......and
has helped me on several occasions.

I often run across situations where the need is to do it in a specific
manner - in your case you want to use a check-list approach. And often it is
not an approach that a full-time DB person would structure. Sometimes one
can re-structure - sometimes not.

So the solution with a checklist approach is to make those checklists to
create an SQL OR statement rather than an AND statement. As you have
discovered the intrinsic approach by Access is an AND statement i.e. those
values are all going on the first level line of the criteria of the query
grid - thus making them and AND statement.

Instead one would need to build a pure sql OR statement from your form,
rather than use the query grid approach.

 

[Duane Hookom]

Ok, forget the un-normalized tables since they seem to be working for you
and "There will be no more added".

Don't continue to forget my question:
"I expected to get an answer from you like "Yes, the results of the query
are
displayed in a report". Do your users see the query or do they see the
results of the query in a form or report?"

Is it too difficult to answer a simple question? You aren't the only person
in need of help that seems to ignore questions from those who try to help.
Maybe we need to ask our questions in the first statement of our replies.

 

[Aviator]

Sorry, no disrespect meant, I thought I had answered. Users see the results
in the query, no need for me to make it pretty in a report.

 

[Duane Hookom]

As I kinda stated earlier it is much easier to filter a report. I don't
generally show datasheet views of tables or queries to users. Build a
report, there cool.

For instance, assuming you have check boxes on your criteria form and the
Tag property of each check box contains the actual field name of the yes/no
field in your report record source. You can use simple code that loops
through the form controls and finds check boxes that are checked. The code
uses the Tag property to build a where clause to open the report. The opened
report will have the records for the checked boxes on your criteria form:

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
'the final strWhere might look like
' [ArmRests] or [TV] or [Carpet] or [Seats] or [Head] or [Over head
Compartment]
'open the report based on the checked boxes
DoCmd.OpenReport "rptCheckList", acViewPreview, , strWhere

 

[Aviator]

I am fairly new at creating code. Before this I shyed away from code knowing
I would need it some day, like now. I have read your code, I think I
understand it. Does the report get the info from the query which gets info
from the form? Or are we skipping the query? Where should the code be
entered? Thanks for your help, it is most appreciated.

As I kinda stated earlier it is much easier to filter a report. I don't
generally show datasheet views of tables or queries to users. Build a
report, there cool.

For instance, assuming you have check boxes on your criteria form and the
Tag property of each check box contains the actual field name of the yes/no
field in your report record source. You can use simple code that loops
through the form controls and finds check boxes that are checked. The code
uses the Tag property to build a where clause to open the report. The opened
report will have the records for the checked boxes on your criteria form:

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
'the final strWhere might look like
' [ArmRests] or [TV] or [Carpet] or [Seats] or [Head] or [Over head
Compartment]
'open the report based on the checked boxes
DoCmd.OpenReport "rptCheckList", acViewPreview, , strWhere

--
Duane Hookom
MS Access MVP

Sorry, no disrespect meant, I thought I had answered. Users see the
results
in the query, no need for me to make it pretty in a report.

 

[Duane Hookom]

Your first post stated "I want to run a parameter query through a form with
those 25 check boxes". Everything I posted is contained in your form.

The report's Record Source is your un-normalized table. Your form has no
record source and just has the check boxes and possibly a command button to
click to open the report. The code would go in the On Click event of the
command button.
--
Duane Hookom
MS Access MVP

I am fairly new at creating code. Before this I shyed away from code
knowing
I would need it some day, like now. I have read your code, I think I
understand it. Does the report get the info from the query which gets info
from the form? Or are we skipping the query? Where should the code be
entered? Thanks for your help, it is most appreciated.

As I kinda stated earlier it is much easier to filter a report. I don't
generally show datasheet views of tables or queries to users. Build a
report, there cool.

For instance, assuming you have check boxes on your criteria form and the
Tag property of each check box contains the actual field name of the
yes/no
field in your report record source. You can use simple code that loops
through the form controls and finds check boxes that are checked. The
code
uses the Tag property to build a where clause to open the report. The
opened
report will have the records for the checked boxes on your criteria form:

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
'the final strWhere might look like
' [ArmRests] or [TV] or [Carpet] or [Seats] or [Head] or [Over head
Compartment]
'open the report based on the checked boxes
DoCmd.OpenReport "rptCheckList", acViewPreview, , strWhere

--
Duane Hookom
MS Access MVP

Sorry, no disrespect meant, I thought I had answered. Users see the
results
in the query, no need for me to make it pretty in a report.

:

Ok, forget the un-normalized tables since they seem to be working for
you
and "There will be no more added".

Don't continue to forget my question:
"I expected to get an answer from you like "Yes, the results of the
query
are
displayed in a report". Do your users see the query or do they see the
results of the query in a form or report?"

Is it too difficult to answer a simple question? You aren't the only
person
in need of help that seems to ignore questions from those who try to
help.
Maybe we need to ask our questions in the first statement of our
replies.

--
Duane Hookom
MS Access MVP


I have the aircraft broken into 25 items(check boxes). What ever
section
is
worked on is what is selected. There will be no more added. A catch
all
"Other" is included. When a user needs to qoute work to be done on a
arm
rest, they would select the check box for seats in the parameter
query
form
that I am trying to get to work. If they need to qoute a job that
will
work
on arm rests and a TV installation for a lear jet, they would select
lear
jet
from the list box of aircraft and the seat check box, and
entertainment
check
box. This, (as I hope) would return the work orders that were from a
lear
jet
that had seat work and/or entertainment work. This would give the
user
a
starting point to determine what they should qoute it for.

:

You don't understand my comments on normalization. You should not
have
fields like "seats", "carpet",... These are data values and should
not
be
field names. How would you handle work done on a new item such as
"Arm
Rests"? You shouldn't have to add fields and controls to maintain a
system.

I expected to get an answer from you like "Yes, the results of the
query
are
displayed in a report". Do your users see the query or do they see
the
results of the query in a form or report?

--
Duane Hookom
MS Access MVP

I tryied doing this database with a combo box instead of check
boxes
and
it
was ineffiecient. This way a user can simple check multiple
categories
of
work that were performed on a job instead of creating a different
record
for
each type of work performed. Example, on an aircraft, work done
on
the
seats,
carpet, windows. The boxes seats, carpet, windows would be
selected
so
when a
user goes to quote a new job they can look back at past work
completed
in
those categories and use the time and cost from those past jobs.
The
query/report will display the work order number so that user can
simple
look
in those work orders and find the info they need.

:

One of your issues is that your table is not normalized. 25
check
box
fields
is not the ideal way to store your data.

However, I expect you want to display your results in a form or
report.
If
so, this question is much easier to answer.
--
Duane Hookom
MS Access MVP

I am creating a database to manage past jobs done for quoting
possible
new
jobs. My database has fields for basic info about the
customer,
than
25
check
box fields with different categories of work completed. One
job(record)
will
have multiple boxes cecked depending on the work completed. I
want
to
run
a
parameter query through a form with those 25 check boxes. I
want
it
so
that
if I select one of those boxes, all records that have that
field
selected,
will be shown. If multiple boxes on the form are selected, any
record
that
has just one of those fields selected, they will be shown.
Help
would
be
greatly appreciated. I have the form created, but have been
unable
to
return
results. No errors messages.

 

[Aviator]

I receive error when running the report. It signals line 3 at the "". What do
I put here? Name of the report? Name of the Form?

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
'the final strWhere might look like
' [Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or

or [Telephone] or [Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

 

[Duane Hookom]

I am having trouble reading your error message. Did you attempt to compile
your code? I kinda expect you might have an issue that can't be seen here
due to line wrapping. Try delete the line(s):
' [Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or

or [Telephone] or
[Various/Miscellaneous]
or [Veneer] or [Windowline]

 

[Aviator]

I deleted that line, still receiver error in the VB, "Compile Error:Invalid
Outside Procedure" It then highlights the "" in the third line of the code.

I am having trouble reading your error message. Did you attempt to compile
your code? I kinda expect you might have an issue that can't be seen here
due to line wrapping. Try delete the line(s):
' [Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or

or [Telephone] or
[Various/Miscellaneous]
or [Veneer] or [Windowline]

--
Duane Hookom
MS Access MVP

I receive error when running the report. It signals line 3 at the "". What
do
I put here? Name of the report? Name of the Form?

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
'the final strWhere might look like
' [Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or

or [Telephone] or
[Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere