How to query records

[mrbboy]

I have a form, frm_Metrix, that I use to tract the number of Reports generated per month. The RecordSource of the form is the query qry_Metrix. Here is the sequel for that.

SELECT Count(*) AS NoOfReport, Year([Date Initiated]) AS [Year Initiated], Month([Date Initiated]) AS [Month Initiated]
FROM tbl_Report
GROUP BY Year([Date Initiated]), Month([Date Initiated]), tbl_Report.Status
HAVING (((tbl_Report.Status)="open"));

This works great. I have a form where I see the number of Reports per month/year in the text box txt_Metrix. txt_Metrix is located in the Detail section of the form. Is it possible to click this text box, txt_Metrix, and just view all the Open Reports for that month?

 

[TheAceMan1]

How are ya mrbboy . . .

My first observation is a need to [blue]include the name of the report in the query![/blue] . . . How else can you tell which report was open!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[mrbboy]

Sorry, the name of the form that I use to view all the reports is frm_Report.

 

[TheAceMan1]

[blue]Is it possible to click this text box, txt_Metrix, and just view all the Open Reports for that month? [/blue]

Using more criteria in your [blue]Having[/blue] clause or setting a filter should do:

[blue]HAVING (Status)="open")) AND ([Year Initiated] = 3) AND ([Month Initiated] = 11) . . .[/blue]

Again, I'm at a lost as to how your handling [blue]Report Names!? . . . (actual report names)[/blue]

And what is the data format of [blue]txt_Metrix ?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886