Assign Values from a Query to unbound controls in a Report (1 Viewer)

[John Lee]

I have a report named rptObjectives and within this report I have 12 unbound controls named as follows:

txtImprovementsJan
txtImprovementsFeb
txtImprovementsMar
txtImprovementsApr
txtImprovementsMay
txtImprovementsJun
txtImprovementsJul
txtImprovementsAug
txtImprovementsSep
txtImprovementsOct
txtImprovementsNov
txtImprovementsDec

I want to populate these with the number of records by month for the current year from the qryContinualImprovementsLog.

The qryContinualImprovementsLog has the following fields:

lngImprovementLogID [Primary Key]
strAreDept
strOpportunitiesForImprovement
strReasonForImprovementDetailsofAchievement
dtmImplementedDate
strAdditionalNotesComments

So based on the dtmImplementedDate field I want to count how many records there are by month for the current year and assign that value to the appropriate unbound control in my report.

I'm thinking that needs to be in the onload event of my report.

Any assistance would be most appreciated.

 

[arnelgp]

take for example, textbox txtImprovementsJan.
you can set it's ControlSource property (in design view) to:

=DCount("1","qryContinualImprovementsLog", "dtmImplementedDate >= #" & DateSerial(Year(Date()), 1, 1) & "# And dtmImplementedDate <= #" & DateSerial(Year(Date()), 2, 0) & "#")

for txtImprovementsFeb, the controlSource:

=DCount("1","qryContinualImprovementsLog", "dtmImplementedDate >= #" & DateSerial(Year(Date()), 2, 1) & "# And dtmImplementedDate <= #" & DateSerial(Year(Date()), 3, 0) & "#")

and so on, and when it goes to Dec

=DCount("1","qryContinualImprovementsLog", "dtmImplementedDate >= #" & DateSerial(Year(Date()), 12, 1) & "# And dtmImplementedDate <= #" & DateSerial(Year(Date())+1, 1, 0) & "#")

 

[Gasman]

If you named your controls better, suffixing with 1, 2 etc, it would easier in a loop.

 

[XPS35]

You don't need twelve unbound fields on your report. That is a very cumbersome solution.
Create a query to calculate the monthly figures and base the report on that query. So, with bound fields.
The query could look like this.

SELECT Month(dtmImplementedDate) AS "Month", Count(*) AS NumberOfImp
FROM qryContinualImprovementsLog
GROUP BY Month(dtmImplementedDate)

I have not tested this and assumed that the base query selects the data from one year.

 

[The_Doc_Man]

A key factor that you didn't describe has to do with what is actually shown in the report. Will you always have the most recent 12 months of monthly counts, or will you only have the monthly counts for the current year. I.e. will your report ALWAYS show 12 counts or will it show 1 to 12 counts depending on the current month?

If you are dealing with the most recent 12 months regardless of year, then the report perhaps shouldn't have unbound fields.

Are you attempting to make the report show values arranged horizontally instead of vertically? Without knowing the underlying reason for having unbound controls on a report, it sounds like you have a design flaw. If we know the reason for not binding, we can perhaps understand your request a bit better and thus give more targeted answers.

 

[mike60smart]

I have a report named rptObjectives and within this report I have 12 unbound controls named as follows:

txtImprovementsJan
txtImprovementsFeb
txtImprovementsMar
txtImprovementsApr
txtImprovementsMay
txtImprovementsJun
txtImprovementsJul
txtImprovementsAug
txtImprovementsSep
txtImprovementsOct
txtImprovementsNov
txtImprovementsDec

I want to populate these with the number of records by month for the current year from the qryContinualImprovementsLog.

The qryContinualImprovementsLog has the following fields:

lngImprovementLogID [Primary Key]
strAreDept
strOpportunitiesForImprovement
strReasonForImprovementDetailsofAchievement
dtmImplementedDate
strAdditionalNotesComments

So based on the dtmImplementedDate field I want to count how many records there are by month for the current year and assign that value to the appropriate unbound control in my report.

I'm thinking that needs to be in the onload event of my report.

Any assistance would be most appreciated.

Are you able to upload a copy of the database?

 

[DHookom]

I would use a crosstab query with a column heading of

"Mth" & Format(Month(dtmImplementedDate), "00")

Count any none blank field as the value and the aggregating field(s) as the row heading.

This should create columns like Mth01, Mth02,…

 

[MarkK]

I asked Claude.ai about this and he actually told a story...

A programmer is being interviewed for a job. The interviewer asks, "Can you give me an example of clean, readable code?"
The programmer proudly shows this:

numberOfTimesTheUserHasClickedTheButtonOnTheMainScreenOfTheApplication = 0

The interviewer stares for a moment and says, "That's... one variable?"
"Yeah," the programmer says. "I like my code to be self-documenting."

 

[Ken Sheridan]

Assuming the report's RecordSource query includes a date column I would add a subquery to the report's RecordSource query's SELECT clause correlating it with the outer query on the year and month of the date:

    (
SELECT
    COUNT(*)
FROM
    qryContinualImprovementsLog AS Q1
WHERE
    YEAR(Q1.dtmImplementedDate) = YEAR(TableNameGoesHere.DateColumnNameGoesHere)
    AND MONTH(Q1.dtmImplementedDate) = MONTH(TableNameGoesHere.DateColumnNameGoesHere);
     ) AS MonthlyCount

You can then bind a control in a monthly group header or footer section to the MonthlyCount column.

Alternatively, if you want the months returned as a list for the current year as a summary independent of the rest of the report, base a subreport in the report header or footer on the following query:

SELECT
    YEAR(dtmImplementedDate) AS CurrentYear,
    MONTH(dtmImplementedDate) AS MonthNumber,
    COUNT(*) AS MonthlyCount
FROM
    qryContinualImprovementsLog
WHERE 
    YEAR(dtmImplementedDate) = YEAR(DATE())
GROUP BY
    YEAR(dtmImplementedDate),
    MONTH(dtmImplementedDate);

You can call the MonthName function in the report to return the month by name rather than number.