Budget/Forecast Load

Budgets and Forecasts typically are NOT date driven. That is, they use column values instead of specific dates. The MonthEnd control is well suited to calculate these values

If your budget application uses 12 months, your Form_Load() routine will look like this:

Sub Form_Load()

Months1.AppEndDate = "12/31"+format(now,'yyyy') ' Gets the last day of this year

Months1.AppNumMonths=12

Months1.Refresh

End Sub

Clicking on the control exposes Month1.FromDateAsColumn to your application (Example 1,3,9, etc.)

Your SQL statement will include these values to get the proper field names:

Sql="Select...."

For I = Months1.FromDateAsCoumn to Months1.ToDateAsColumn

SQL=SQL+"BUDQTY"&I &"BUDAMT"&I&","

NEXT I

SQL=LEFT(SQL,LEN(SQL)-1) ' STRIP OFF LAST COMMA

SQL=SQL+ "...Additional Statements "

RESULTS: SELECT ....,BUDQTY3,BUDAMT3,BUDQTY4,BUDAMT4...BUDQTY10...FROM....

Similarly, a Forecast Load statement might look like this:

Sub FORM_LOAD()

Months1.AppEndDate = "12/31"+STR(Format(now,'yyyy')+1) ' Gets the last day of next year

Months1.AppNumMonths=24

Months1.Refresh

END SUB

Now you have all of the current year plus all of next year, for 24 months. Your Forecast fields should have the number of the month in them, like FCST01, FCST02, etc. You can also, of course, call your fields FCSTJan,FCSTFeb... Then you'll convert your SQL statement to:

FOR I=Months1.FromDateAsColumn to Months1.ToDateAsColumn

SQL=SQL+"FCST"&Format(Months1.MontEnd(I), """mmm""")&","   REM Three Double Quotes around mmm

NEXT

RESULT: FCSTJan,FCSTFeb,......