Create Access Form Title & Show/Hide Control Based on Parameter Query Results

Hello Experts!  I am stuck and need your help.  I have a 2 part request:

First, using Access 2013, I am trying to create a title in a form based on the user's entry in a parameter query on the date.  I have done this before with a report where in the query, I used:
(QRY1)
Field: SalesYear
Table: TBL_CompanySales
Criteria: Between [Enter Start Year] And [Enter End Year]

Then I created a title for the Report by using a textbox with the formula:
(MainForm1)
="Total Sales from " & [Enter Start Year] & " and " & [Enter End Year] & ""

However, in Form View, this title formula creates a #Name? error. Is it possible to pass content from a parameter query to a textbox in Form View?  I know the obvious answer is to print it to a report, but the client wants to filter the data in Form View with data entry capabilities and see the title change to match the years they select.  (Note - the parameter query is sorting SubForm1 datasheet.)  

Second, I would like the controls that contain sales totals to appear or disappear based on the user's entry in a parameter query on the date (i.e., if the user enters "2010" Start Year and "2012" End Year, the fields containing totals from other years are hidden (Visible = False))  (Note - the sales totals are individual controls for each year in the main form.)

I'm thinking of creating a command button in Form View that when clicked, it will filter years selected in the datasheet and hide totals from other years, but I'm not sure if that is the best approach.

Thank you!
danger_kittyAsked:
Who is Participating?

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

x
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.

Jeffrey CoachmanMIS LiasonCommented:
Hello Experts!  I am stuck and need your help.  I have a 2 part request:
No, ...You have asked two separate questions,  requiring two different approaches...
Jeffrey CoachmanMIS LiasonCommented:
I am working on the main question now....
Others will reply as well....
Jeffrey CoachmanMIS LiasonCommented:
Note hat the the spelling in each Parameter prompt must be EXACTLY the same in all places.
For example: "Enter Start Year", wont work with: "Enter Starting Year"

So first check your spelling carefully...
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jeffrey CoachmanMIS LiasonCommented:
If it were me, I would not include the parameter in the query.

Instead, I would just put two textboxes on the form itself to do the filtering... (this is the more common approach, besides making a separate "Select Year" pop-up form)

Parameters allow user to type whatever they like.
With a textbox you can add date validation.

A parameter will also always present all the same prompts.
So in your case if they wanted 2010 through 2015
They would enter the two prompts.
If they then wanted 2010 through 2012, ...they are forced to enter the start and end year again.

Sample simple attached
Database106.mdb
Jeffrey CoachmanMIS LiasonCommented:
Your syntax is incorrect, ...try:

="Total Sales from " & [Enter Start Year] & " and " & [Enter End Year]
danger_kittyAuthor Commented:
Thank you Jeffrey for your response.  Do I need to break this into 2 questions then?  My apologies - I haven't used this forum for awhile.

Also, I copied and pasted the query and formula directly from the database.  It appears that it matches in text and punctuation - unless I missed something?
Jeffrey CoachmanMIS LiasonCommented:
...as the control source for the report textbox...

So ignore my "Form" solution and see this new Report sample.


Sorry for the confusion...
;-)

Jeff
Database106.mdb
danger_kittyAuthor Commented:
I'll give it a whirl - thx!
Jeffrey CoachmanMIS LiasonCommented:
screenshot

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
Jeffrey CoachmanMIS LiasonCommented:
As far as your secon question goes...
I am not quite sure why you have individual year totals as controls/fileds on the report?

If you "grouped" the report by the year, then only the selected years would be visible, hence they are the only totals that would show.
see the new sample
GroupingDatabase106.mdb
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.