Populate Text box in Repoprt depending on value entered in Parameter

[Farida]

Hi, I have the Form with details below.
Form name - [MOC Creation Stats]
Query used in the form - [CREATED_STATISTICS]


Parameter name - [MOC Number Starts With]
Query -
PARAMETERS [MOC Number Starts With] Text ( 255 );
TRANSFORM Count(MonthName(Month([CREATED DATE]))) AS ['NUMBER']
SELECT MonthName(Month([CREATED DATE])) AS ['MONTH'], Count(MOC_DATA.[MOC NUMBER]) AS [Total Of MOC_NUMBER]
FROM MOC_DATA
WHERE (((MOC_DATA.[MOC NUMBER]) Like "*" & [MOC Number Starts With] & "*"))
GROUP BY MonthName(Month([CREATED DATE])), Month([CREATED DATE])
ORDER BY Month([CREATED DATE])
PIVOT MOC_DATA.ASSET;

When the report is loaded, it asks for Paramater value MOC number stats with :

I want to populate the textbox as below
If value entered is CR12 - then 2012
If value entered is CR13 - then 2013
If value entered is CR14 - then 2014
If value entered is blank - overall

Please advise.
----------------------
My tries as below.
1. Set Control Source of textbox to =[Reports]![MOC CREATION STATS]![MOC Number Starts With].

this doesnt work. In turn i get #Name? in my textbox.

[pbaldy]

Try just:

=[MOC Number Starts With]

[Farida]

No, this doesnt work. I am getting the same error #Name? in the textbox

[pbaldy]

Can you post the db here?

[Farida]

How can i do this? Its a big database.

[pbaldy]

You could export that report, query and table to a db, then zip it.

[Farida]

You could export that report, query and table to a db, then zip it.

hERE'S my zipped file. I have many queries here.

[Farida]

Question one - I have created a parameter form YEAR_FORM to accept value. But it does not get called. I referred to https://support.office.com/en-nz/art...bmform_param_1 to do so.
Question two - Secondly i want to populate the YEAR in the header of the report based on the value entered in the YEAR_FORM
Question three - The Parameter prompts twice for printing the report. How can i avoid this?
Question Four - I get below error while report generation for the year 2011. This is because this created records only from June month onwards. How can i rectify this?
Microsoft Access database engine does not recognize " as valid field name or expression

Your expertise solutions are much appericiated. Thanks.

[pbaldy]

To the original question, you seem to be using a form now, and you have the wrong reference. It should be:

=[Forms]![YEAR_FORM]![YEAR_COMBO]

I'll try to look at the others later, must eat breakfast now before I get cranky.

[pbaldy]

Regarding other questions:

3) I don't get prompted when the report opens, as long as the form is open.
4) I don't do much charting, but the crosstab query returns fewer column for 2011. Something in the report is looking for columns that don't exist, and needs to be adjusted to be more dynamic. I may be able to look at it later. Don't see where it is right off, but my lack of chart knowledge is probably getting in the way.

[Farida]

Thanks for your responses Paul. I am still looking for an answer to my below query. Yes the result here is generated from the month of June rather than January and that i causing the error message to pop up. Can you please provide a solution to this. Thanks.

Question Four - I get below error while report generation for the year 2011. This is because this created records only from June month onwards. How can i rectify this?

[June7]

Could change the combobox RowSource to an SQL statement:

SELECT DISTINCT Year([Created Date]) FROM MOC_Data;

I am not getting any error message for the report for any year selected. However, 2011 data doesn't display in the chart, it shows 2012 data. I don't understand why 2011 data won't display. The CROSSTAB query works.

I have never seen apostrophe used as part of a field name. Is this because Month and Number are reserved words? Would be better to use something else for names.