Query by Form with CheckBoxes

[Guest]

Hello,

I have Access 2003. I tried to get information on this before, but perhaps
I didn't explain it well.

I have a table named Category with several fields named GumChewing,
Sweetener, Flavor, etc. and these fields have a Data Type of Yes/No and a
Format of True/False. The Default Value is False and the Display Control for
these fields is Check Box.

I have a query that uses the Category table and has criteria for the fields
as follows:
Like "*" & [Forms]![F_CatSearch]![txtGumChewing]
Like "*" & [Forms]![F_CatSearch]![txtSweetener]
Like "*" & [Forms]![F_CatSearch]![txtFlavor]


I wanted a form with check boxes for GumChewing, Sweetener, Flavor, etc.
where the user could check the items they want and with the click of a button
the output would be any items which contained only the items they checked.

Since I couldn't figure this out, I have a form with several Text Boxes
named txtGumChewing, txtSweetener, txtflavor, etc. At this time, the user
can input -1 for each field they want to select and it pulls the correct
data. It would be nice if I could at least figure out how to allow the user
to use a "y" for "yes" instead of the "-1".

The closest I could come was to change the criteria for the fields in the
Query to something like the following. Entering "Y" works, but it prompts
for each field instead of allowing me to put a "Y" in the box on the form.

IIf( [Forms]![F_CatSrch]![cboGumChewing] = "y", -1, 0 )

Any help would be greatly appreciated!

 

[Al Campagna]

LD,
Looks as though your query will always deliver all records from Category, because of
the LIKE. And, "Like" really has no meaning against a T/F field. It is either True or
not.
You indicate the criteria, but you don't describe the fields the criteria pertain to.
What field in Category will txtGumChewing be the criteria, and what possible values does
that Category field have. It should be a T/F field itself... not a field that contains
various textual values.
Please describe further...

I'll assume that the 3 fields in Category are themselves T/F.

Field ChewingGum in the query should have a criteria of...
= [Forms]![F_CatSearch]![txtGumChewing]
Field Sweetner...
= [Forms]![F_CatSearch]![txtSweetener]
etc...

If GumChewing on the form is True, only records from Category, where GumChewing is
True, will be returned.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

I have a table which contains patents. Each patent can fall into multiple
categories, eg gum, sweetener, etc. I need to be able to go to a form and
check of all the categores and pull up all the patents which have these
categories.

LD,
Looks as though your query will always deliver all records from Category, because of
the LIKE. And, "Like" really has no meaning against a T/F field. It is either True or
not.
You indicate the criteria, but you don't describe the fields the criteria pertain to.
What field in Category will txtGumChewing be the criteria, and what possible values does
that Category field have. It should be a T/F field itself... not a field that contains
various textual values.
Please describe further...

I'll assume that the 3 fields in Category are themselves T/F.

Field ChewingGum in the query should have a criteria of...
= [Forms]![F_CatSearch]![txtGumChewing]
Field Sweetner...
= [Forms]![F_CatSearch]![txtSweetener]
etc...

If GumChewing on the form is True, only records from Category, where GumChewing is
True, will be returned.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,

I have Access 2003. I tried to get information on this before, but perhaps
I didn't explain it well.

I have a table named Category with several fields named GumChewing,
Sweetener, Flavor, etc. and these fields have a Data Type of Yes/No and a
Format of True/False. The Default Value is False and the Display Control for
these fields is Check Box.

I have a query that uses the Category table and has criteria for the fields
as follows:
Like "*" & [Forms]![F_CatSearch]![txtGumChewing]
Like "*" & [Forms]![F_CatSearch]![txtSweetener]
Like "*" & [Forms]![F_CatSearch]![txtFlavor]


I wanted a form with check boxes for GumChewing, Sweetener, Flavor, etc.
where the user could check the items they want and with the click of a button
the output would be any items which contained only the items they checked.

Since I couldn't figure this out, I have a form with several Text Boxes
named txtGumChewing, txtSweetener, txtflavor, etc. At this time, the user
can input -1 for each field they want to select and it pulls the correct
data. It would be nice if I could at least figure out how to allow the user
to use a "y" for "yes" instead of the "-1".

The closest I could come was to change the criteria for the fields in the
Query to something like the following. Entering "Y" works, but it prompts
for each field instead of allowing me to put a "Y" in the box on the form.

IIf( [Forms]![F_CatSrch]![cboGumChewing] = "y", -1, 0 )

Any help would be greatly appreciated!

 

[Al Campagna]

LD,
Then T/F checkboxes will not work "directly."
You need to express the problem as... "Show me all the Patent records where the string
value in Category = some string value.
That some string value should be something like "Gum Chewing" or "Sweetner"... Not True or
False. (Category can't be "Like" True or False, as you had it originally)
A simple solution would be to use 3 combos or 3 listboxes (either will do but I prefer
combos) that list all the possible Categories of Patents in each one.
The user would pick one Category from each combo.

Then, the criteria in the report query against Category would be...
= [Forms]![F_CatSearch]![cboCat1] OR [Forms]![F_CatSearch]![cboCat2] OR
[Forms]![F_CatSearch]![cboCat3]

If you take that same criteria, and add the Like operator, any cboCat left blank would
deliver ALL Categories, so I don't think that will apply here.
When all combos are Null, no records are returned.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a table which contains patents. Each patent can fall into multiple
categories, eg gum, sweetener, etc. I need to be able to go to a form and
check of all the categores and pull up all the patents which have these
categories.

LD,
Looks as though your query will always deliver all records from Category, because of
the LIKE. And, "Like" really has no meaning against a T/F field. It is either True or
not.
You indicate the criteria, but you don't describe the fields the criteria pertain
to.
What field in Category will txtGumChewing be the criteria, and what possible values
does
that Category field have. It should be a T/F field itself... not a field that contains
various textual values.
Please describe further...

I'll assume that the 3 fields in Category are themselves T/F.

Field ChewingGum in the query should have a criteria of...
= [Forms]![F_CatSearch]![txtGumChewing]
Field Sweetner...
= [Forms]![F_CatSearch]![txtSweetener]
etc...

If GumChewing on the form is True, only records from Category, where GumChewing is
True, will be returned.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,

I have Access 2003. I tried to get information on this before, but perhaps
I didn't explain it well.

I have a table named Category with several fields named GumChewing,
Sweetener, Flavor, etc. and these fields have a Data Type of Yes/No and a
Format of True/False. The Default Value is False and the Display Control for
these fields is Check Box.

I have a query that uses the Category table and has criteria for the fields
as follows:
Like "*" & [Forms]![F_CatSearch]![txtGumChewing]
Like "*" & [Forms]![F_CatSearch]![txtSweetener]
Like "*" & [Forms]![F_CatSearch]![txtFlavor]


I wanted a form with check boxes for GumChewing, Sweetener, Flavor, etc.
where the user could check the items they want and with the click of a button
the output would be any items which contained only the items they checked.

Since I couldn't figure this out, I have a form with several Text Boxes
named txtGumChewing, txtSweetener, txtflavor, etc. At this time, the user
can input -1 for each field they want to select and it pulls the correct
data. It would be nice if I could at least figure out how to allow the user
to use a "y" for "yes" instead of the "-1".

The closest I could come was to change the criteria for the fields in the
Query to something like the following. Entering "Y" works, but it prompts
for each field instead of allowing me to put a "Y" in the box on the form.

IIf( [Forms]![F_CatSrch]![cboGumChewing] = "y", -1, 0 )

Any help would be greatly appreciated!

 

[Guest]

Al,

Thank you for your explanations and assistance.

LD

LD,
Then T/F checkboxes will not work "directly."
You need to express the problem as... "Show me all the Patent records where the string
value in Category = some string value.
That some string value should be something like "Gum Chewing" or "Sweetner"... Not True or
False. (Category can't be "Like" True or False, as you had it originally)
A simple solution would be to use 3 combos or 3 listboxes (either will do but I prefer
combos) that list all the possible Categories of Patents in each one.
The user would pick one Category from each combo.

Then, the criteria in the report query against Category would be...
= [Forms]![F_CatSearch]![cboCat1] OR [Forms]![F_CatSearch]![cboCat2] OR
[Forms]![F_CatSearch]![cboCat3]

If you take that same criteria, and add the Like operator, any cboCat left blank would
deliver ALL Categories, so I don't think that will apply here.
When all combos are Null, no records are returned.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a table which contains patents. Each patent can fall into multiple
categories, eg gum, sweetener, etc. I need to be able to go to a form and
check of all the categores and pull up all the patents which have these
categories.

LD,
Looks as though your query will always deliver all records from Category, because of
the LIKE. And, "Like" really has no meaning against a T/F field. It is either True or
not.
You indicate the criteria, but you don't describe the fields the criteria pertain
to.
What field in Category will txtGumChewing be the criteria, and what possible values
does
that Category field have. It should be a T/F field itself... not a field that contains
various textual values.
Please describe further...

I'll assume that the 3 fields in Category are themselves T/F.

Field ChewingGum in the query should have a criteria of...
= [Forms]![F_CatSearch]![txtGumChewing]
Field Sweetner...
= [Forms]![F_CatSearch]![txtSweetener]
etc...

If GumChewing on the form is True, only records from Category, where GumChewing is
True, will be returned.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,

I have Access 2003. I tried to get information on this before, but perhaps
I didn't explain it well.

I have a table named Category with several fields named GumChewing,
Sweetener, Flavor, etc. and these fields have a Data Type of Yes/No and a
Format of True/False. The Default Value is False and the Display Control for
these fields is Check Box.

I have a query that uses the Category table and has criteria for the fields
as follows:
Like "*" & [Forms]![F_CatSearch]![txtGumChewing]
Like "*" & [Forms]![F_CatSearch]![txtSweetener]
Like "*" & [Forms]![F_CatSearch]![txtFlavor]


I wanted a form with check boxes for GumChewing, Sweetener, Flavor, etc.
where the user could check the items they want and with the click of a button
the output would be any items which contained only the items they checked.

Since I couldn't figure this out, I have a form with several Text Boxes
named txtGumChewing, txtSweetener, txtflavor, etc. At this time, the user
can input -1 for each field they want to select and it pulls the correct
data. It would be nice if I could at least figure out how to allow the user
to use a "y" for "yes" instead of the "-1".

The closest I could come was to change the criteria for the fields in the
Query to something like the following. Entering "Y" works, but it prompts
for each field instead of allowing me to put a "Y" in the box on the form.

IIf( [Forms]![F_CatSrch]![cboGumChewing] = "y", -1, 0 )

Any help would be greatly appreciated!