Hi,
My database consists of a fin_Account table that contains the following fields
-account which is of the format
###-##-###-######-###-#-######
-account description
-month_x_actual which refers to the balance for month x
-month_x_prev whic refers to the balance for month x in the prev yr
-month_x_budget which refers to the budget for month x
(The table consists of month actual,month_prev and month budget for all 12 months. So the table has fields month_1_actual,month_2_actual,month_3_actual
month_1_budget,month_2_budget,month_3_budget etc)
Now the report I want to generate is based on an account that starts with
001-21-712-813000-207-1-* .
I have a form where the user choses a particular month for which they want the report. Now depending on the month that the user choses I would need to select the corresponding column in my report.
For example if the user choses january as the month then my sql for the report record source should look something like this
select month_1_actual as month_actual
month_1_budget as month_budget
month_1_prev as month_prev
from fin_Account
So I need to set the record souce of the report based on the month that the user enters.
Currently when the user clicks the 'generate' button on the form I open the report. In the report OnLoad event I have entered the following code.
On_Load()
Dim theMonth As String
Dim fiscalMonth As String
Dim db As Database
'theMonth stores the month the user chose
theMonth = Forms!activity_level_Summary_rpts!lstPeriod
Dim strSql As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("rehab month"
'get the numeric fiscal equivalent of the chosen month
fiscalMonth = getPeriod(theMonth)
'create the select statemnet based on the month
strSql = "Select MONTH_" & fiscalMonth & "_PREV_YEAR AS MONTH_PREV, MONTH_" & fiscalMonth & "_CUR_YEAR AS MONTH_ACTUAL,MONTH_" & fiscalMonth & "_BUDGET AS MONTH_PLAN,ACCOUNT FROM FINANCE_INF_ACCOUNT WHERE ACCOUNT LIKE '001-21-712-813000-207-1-*'"
db.CreateQueryDef "rehab_month", strSql
Me.RecordSource = "rehab_month"
Set db = Nothing
End If
I am not sure if I am doing this correcty. Is it possible to change the record source property in the onload event of the report or should I change it somewhere else.
My report should look something like this
---------------------------------
Month:April 2003
actual_last_yr actual this_yr Budget
Admission (select cols for account 001-21-712-813-207- 1-4011240)
Patient days (select cols for account 001-21-712-813-207- 1-4031240)
Adult_Child (select cols for account 001-21-712-813-207-1-4000000 and 001-21-712-813-207-1-4000100)
So I want to make controls for each row where I basically just select the appropriate column from the rehab_query that is my report source.
So the admissions for the prev_year column would have something like this in the control box
select month_prev from rehab_month where account='001-21-712-813-207-1-4011240'
I have tried doing this but its not workign correctly I think it has to do with the report source of my report.
Any help would greatly be appreciated.
Thanks.
sync123
My database consists of a fin_Account table that contains the following fields
-account which is of the format
###-##-###-######-###-#-######
-account description
-month_x_actual which refers to the balance for month x
-month_x_prev whic refers to the balance for month x in the prev yr
-month_x_budget which refers to the budget for month x
(The table consists of month actual,month_prev and month budget for all 12 months. So the table has fields month_1_actual,month_2_actual,month_3_actual
month_1_budget,month_2_budget,month_3_budget etc)
Now the report I want to generate is based on an account that starts with
001-21-712-813000-207-1-* .
I have a form where the user choses a particular month for which they want the report. Now depending on the month that the user choses I would need to select the corresponding column in my report.
For example if the user choses january as the month then my sql for the report record source should look something like this
select month_1_actual as month_actual
month_1_budget as month_budget
month_1_prev as month_prev
from fin_Account
So I need to set the record souce of the report based on the month that the user enters.
Currently when the user clicks the 'generate' button on the form I open the report. In the report OnLoad event I have entered the following code.
On_Load()
Dim theMonth As String
Dim fiscalMonth As String
Dim db As Database
'theMonth stores the month the user chose
theMonth = Forms!activity_level_Summary_rpts!lstPeriod
Dim strSql As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("rehab month"
'get the numeric fiscal equivalent of the chosen month
fiscalMonth = getPeriod(theMonth)
'create the select statemnet based on the month
strSql = "Select MONTH_" & fiscalMonth & "_PREV_YEAR AS MONTH_PREV, MONTH_" & fiscalMonth & "_CUR_YEAR AS MONTH_ACTUAL,MONTH_" & fiscalMonth & "_BUDGET AS MONTH_PLAN,ACCOUNT FROM FINANCE_INF_ACCOUNT WHERE ACCOUNT LIKE '001-21-712-813000-207-1-*'"
db.CreateQueryDef "rehab_month", strSql
Me.RecordSource = "rehab_month"
Set db = Nothing
End If
I am not sure if I am doing this correcty. Is it possible to change the record source property in the onload event of the report or should I change it somewhere else.
My report should look something like this
---------------------------------
Month:April 2003
actual_last_yr actual this_yr Budget
Admission (select cols for account 001-21-712-813-207- 1-4011240)
Patient days (select cols for account 001-21-712-813-207- 1-4031240)
Adult_Child (select cols for account 001-21-712-813-207-1-4000000 and 001-21-712-813-207-1-4000100)
So I want to make controls for each row where I basically just select the appropriate column from the rehab_query that is my report source.
So the admissions for the prev_year column would have something like this in the control box
select month_prev from rehab_month where account='001-21-712-813-207-1-4011240'
I have tried doing this but its not workign correctly I think it has to do with the report source of my report.
Any help would greatly be appreciated.
Thanks.
sync123