MTD YTD, actual and forecast report using query

[sandease]

I have a report that lists two types of numbers; actual and forecast. Let's say I'm printing the report during the month of March. I want Jan and Feb columns to show actual numbers and March through Dec to show forecast numbers. This report sums the columns based on job#. I have one query with all numbers grouped by job. I have a form that captures today's date and I can parse out the current month from that. How do I get the column headings and numbers to automatically change from Forecast to Actual as we go through the year? I don't know how to 'plug' the amount fields from the query into the appropriate columns in the report because for example, March will be Forecast numbers this month and Actual numbers when I'm printing the April report. In the total section of the report, using VBA I use a case statement to determine what month it is and then I've tried Me.qryname.fieldname or [query]![queryname]![fieldname] and I keep getting object required. What am I doing wrong? I don't understand object oriented programming yet and am trying to grasp what Access is asking me.

 

[Remou]

Hi
If you have a report with a query called, say, qryResults,
attached to a report, you could try a few of these ideas.
[tt]In a text box
=IIf(Month([timestamp])<Month(Date()),"Actual","Expected")
Where txtDate is a Textbox containing a date:
=IIf(Month([txtDate])<Month(Date()),"Actual","Expected")
Where [Actual] and [Expected] are fields in your query:
=IIf(Month([txtDate])<Month(Date()),[Actual],[Expected])[/tt]

I hope I have understood what you are trying to acheive.

 

[sandease]

Thank you, this is so much easier than I was trying to do.