Dynamically generating a report title based upon a date criteria

[Johnny Drama]

Hello all:

I currently have a report setup and the higher ups would like to generate the report by quarters or YTD when ever they feel like it.

I've created buttons that allow them to simply click for the prior quarter, the current quarter, or YTD. The problem is I would like the report title to specify which time period the data reflects. For example, if today I press the button for the current quarter report I would like the report title to include "1st Quarter" or something like that.

The criteria I'm using to pull the data is below, but I'm not sure if that matters or not. Year([Reports]![CPE]![CourseDate])=Year(Now() And DatePart("q",Date())=DatePart("q", Now())

Access 2010

Thanks in advance for any help.

 

[plog]

I'd do this in VBA using the button's onclick method. You add a report title to your form called RptTitle, then when the button is clicked you compose what the title should be and add it to the caption of RptTitle:

  str_Title= "Dynamic Report Title"

  Screen.ActiveReport.RptTitle.Visible = True
  Screen.ActiveReport.RptTitle.Caption = str_Title

 

[Johnny Drama]

Hey plog,

That makes sense to me up to a point. In your example, where would the value for the str_title come from? Where would that value be grabbed from to dynamically change the title from "First Quarter" or whatever, to "Second Quarter"?

Thanks

 

[plog]

From your setup, it sounds like you have a button labeled something like "Current Quarter". In that instance you would hard code the value:

str_Title="Current Quarter"

 

[Johnny Drama]

No, there are "Prior Quarter", "Current Quarter" and "Year to Date" buttons. The Year to Date button will work with what you've suggested, but the Prior and Current quarter buttons will not. Example, if I click the Current Quarter button today I would like the report to be titled "First Quarter", if I click the same button in April the report needs to be titled "Second Quarter"

 

[plog]

Ok, in that instance you could use the DatePart function:

http://www.techonthenet.com/access/functions/date/datepart.php

If they clicked on the "Current Quarter" button you would set str_Title like this:

str_Title = "Quarter " & DatePart("q", Date()) & " Report"

To get exactly what you wanted (e.g. "Second Quarter") you would need a case to convert the 2 to "Second".