Access Parameter Query Based on User Form Check Box Selection

Hi All,

I have a form where a user can select Location and Week No as options for a report. These are selected as ranges [From Location], [To Location] using combo boxes.

My manager has asked me to also add in location type selections using Check Boxes.

In response to his request, I have assembled 5 Check Boxes on the form for the user to select. Note, the user can only select one option at a time. Their names are ChkAll, ChkAmbient, ChkChiller, ChkFreezer, ChkProduce.

If the user selects ChkAll, then all location types are reported. If the user selects Freezer, then only Freezer Locations are reported and so forth.

My current parameter query that generates the report uses the following parameters:

Location: >=[Forms]![UF_REPORT_SELECTION]![DCStart] And <=[Forms]![UF_REPORT_SELECTION]![DCEnd]

Week: >=[Forms]![UF_REPORT_SELECTION]![WeekFrom] And <=[Forms]![UF_REPORT_SELECTION]![WeekTo]

I already have tables with locations and locations types.

Is anyone able to help me out with this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Add a hidden field to your form, and update that field when the user clicks one of your checkboxes, then have your param query check that hidden field to get the data to filter for Location. For example, if the "Freezer" location has a value of 1, then do this in the chkFreezer's AfterUpdate event:

Me.YourHiddenTextbox = 1

Now add the Forms!UF_REPORT_SELECTION.YourHiddenTextbox paramenter to query.

Note: Use "*" (without the quotes) to indicate ALL locations.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
try this

Where Location Like choose(Forms!UF_REPORT_SELECTION!LocationRange, "*","Ambient","Chiller","Freezer","Produce"))
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Rey Obrero (Capricorn1)Commented:
try this

Where Location Like choose(Forms!UF_REPORT_SELECTION!LocationRange, "*","Ambient","Chiller","Freezer","Produce")
creativefusionAuthor Commented:
Excellent. I do not know why I did not think of that. Great help thanks.

Did you use individual checkboxes or an option group?
This is tailor made for an option group.

Rub out your present boxes (or hide them first)
Add a image group
tool bar icon for option groupWalk through the wizard.
Wizard startPick a default if you want one
 default Value selectionPick what value the option group control will have when each option is picked.
I went with the defaults, but if you have a table, then go with the primary key values for those that exist, and say * for All
 select valuesComplete the wizard
 completed groupRe-arranged the controls to suit

LocationType : = [Forms]![UF_REPORT_SELECTION]![optGroupLocationType]   <-------------------My name, change to suit

if the All as * doesn't play correctly, make All have a value of -1
then do an iif and play till query shows all records.
This might do it

LocationType : = iif( [Forms]![UF_REPORT_SELECTION]![optGroupLocationType] <> -1, [Forms]![UF_REPORT_SELECTION]![optGroupLocationType] , is not null)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.