Rolling Months within a Report

[RayMilhon]

I have a report in Access 2010 The Report is run monthly and has 5 Sections Each with a membership count by Line of business(there are 2) and a total. The query runs perfectly and the report is good with 1 Exception in the page header I have the Section headings with the column headings underneath 3 are unbound text boxes for the 3 previous months. for example running today would have Mar 2013, Apr 2013, and May 2013 as the section heading for those 3. I've put code in the detail section of the report so those 3 text boxes are populated with the correct information. However when I preview the report all 3 of those text boxes are blank. Anyone know the sequence is the page header populated prior to the detail format event? where else could I put this where it would work?

[RayMilhon]

Never mind I figured it out. Thanks

[ssanfu]

Would you care to post the solution? I would be interested in your solution.

[RayMilhon]

I have a field in the query for the date of eligibility which is the first of each month. I was trying to use that field to set the headings but the order of operations in Access the textbox was populated before I could use the field. I then realized that the dates were constant. It would always be the previous 3 months so June would be March April and May, July would be april, May and June. Simply set the text boxes = DateAdd("m",-3,firstofmonth()) changing the -3 to -2 and -1.

FirstofMonth is a SQL Server function that returns the first of a given month based on the parameter @d1

firstofmonth(0) gives the first of the current month (1) previous month (2) month before that etc.

CREATE FUNCTION [dbo].[firstofmonth] 
(
	-- Add the parameters for the function here
	@d1 numeric
)
RETURNS Date
AS
BEGIN
	-- Declare the return variable here
	DECLARE @firstofmonth date
	DEclare @m1 date
	Declare @d2 as numeric
	If (@d1 >= 0) 
	  set  @d2=@d1*-1
	set @m1 = DateAdd(m,@d2,getdate())
	
	-- Add the T-SQL statements to compute the return value here
    set @firstofmonth = Cast(str(Month(@m1)) + '/01/' + str(year(@m1)) as date)
	-- Return the result of the function
	RETURN @firstofmonth;

END

[RayMilhon]

Oops confused myself the Code I posted was for a different report that gets the first of the month for each of the previous 12 months. The code I should have posted is in an Access Module

Public Function firstofmonth() as date

firstofmonth = CDate(Month(Date()) & "/01/" & year(Date()))
End Function

[RayMilhon]

sorry about that been a long day