Hi there - I understand the basics of doing calculated values on a form but a bit stuck on this more complex case
This is an example of the underlying query
Which generates
I then have this on an underlying subform
I want to have a field on here which is Profit which is Sales minus Cost of Sales
On my main form I am setting the source SQL to this form via VBA e.g. where the month is February and CAB is chosen..
Case 0
Me.FrmMonthDeptQuery.Form.SumOfFeb.ColumnHidden = False
FrmMonthDeptQuery.Form.RecordSource = "SELECT Forecast.Income_statement_section, Forecast.Dep, Sum(Forecast.Feb) AS SumOfFeb FROM Forecast GROUP BY Forecast.Income_statement_section, Forecast.Dep HAVING (((Forecast.Dep)='CAB'));"
How should I add a field to the subform and calculate it during the VBA I assume e.g. in this case profit would be sales of 296,681.63 - COS of 254,890.00 = 36,791.63
Thanks
Paul
This is an example of the underlying query
Which generates
I then have this on an underlying subform
I want to have a field on here which is Profit which is Sales minus Cost of Sales
On my main form I am setting the source SQL to this form via VBA e.g. where the month is February and CAB is chosen..
Case 0
Me.FrmMonthDeptQuery.Form.SumOfFeb.ColumnHidden = False
FrmMonthDeptQuery.Form.RecordSource = "SELECT Forecast.Income_statement_section, Forecast.Dep, Sum(Forecast.Feb) AS SumOfFeb FROM Forecast GROUP BY Forecast.Income_statement_section, Forecast.Dep HAVING (((Forecast.Dep)='CAB'));"
How should I add a field to the subform and calculate it during the VBA I assume e.g. in this case profit would be sales of 296,681.63 - COS of 254,890.00 = 36,791.63
Thanks
Paul