MS Access calculation based on a control name rather than a control source (field)

Question

I'm creating an MS Access report based on a dynamic SQL query (a stored procedure) that outputs fields based on the current date, this means that the fields it delivers when run change depending on the current date when the query is run.

I've created a vba script that dynamically changes the Access report's control sources using the same calculation that SQL server uses to output the fields (this seems to be working OK), however I now want to create some sum calculations based on the values of those dynamic controls but I don't seem to be able to use the control names in the calculation (rather than the control source).

E.G.

• There is currently a (dynamic) field output from my SQL query called "201606 P", along with static fields "Person" and "Project".
• MS Access report is opened.
• MS Access vba script does the same calculation as happens in the SQL query and changes the control named "Date1P" to have a control source of "201606 P".
• The report is grouped by person, and I want to calculate the sum of the "201606 P" field for each person.
  • My sum 'unbound' textbox control has a control source of "=Sum([Date1P])", with Date1P being the NAME of a control I want to sum in the group, rather than being a field provided in the query.
• When opened Access presents a parameter box asking for the value of [Date1P].

Can I calculate one control using just the name of another control, rather than the control source for that control (which I can't know before the query is run)?

Should I just work around it and do more scripting to dynamically insert the control sources for the sum fields?

Answer 1

Okay - I think I found the missing quote and comma - I added two new columns TotalsByDateP - should be the one you want

declare @sql as varchar(max); 
select @sql = 'SELECT Person, Project, [Total P], [Total TS], 
' + stuff(( select distinct ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then PlanHours end), 0) as [' + [SortDate] + ' P]' 
+ ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then TimesheetHours end), 0) as [' + [SortDate] + ' TS]',
' + stuff(( select distinct ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then PlanHours end), 0) as [TotalsByDateP]' 
+ ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then TimesheetHours end), 0) as [TotalsByDateTS]' 
from qryPreviousStaticQueryThatProvidesRecords for xml path('')), 1, 1, ''); 
select @sql += ' FROM qryPreviousStaticQueryThatProvidesRecords GROUP BY Person, Project, [Total P], [Total TS];'; PRINT @sql; exec(@sql);

Let me know if this runs okay

If so you should see a new field called "TotalsByDateP" in our Report query. Change the controlsource for the totals fields to "TotalsByDateP" field - and the grouping sum field would be "=Sum([TotalsByDateP])"

Answer 2

You can try this SQL if you want - you should see a new field called "TotalsP" in our Report query. Change the controlsource for the totals fields to "TotalsP" field - and the grouping sum field would be "=Sum([TotalsP])"

I think I have all the commas and quotation marks lined up.

declare @sql as varchar(max); 
select @sql = 'SELECT Person, Project, [Total P], [Total TS], 
' + stuff(( select distinct ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then PlanHours end), 0) as [' + [SortDate] + ' P],'
' + stuff(( select distinct ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then PlanHours end), 0) as [TotalsP]' 
+ ',ISNULL(max(case [SortDate] when ''' + [SortDate] + ''' then TimesheetHours end), 0) as [' + [SortDate] + ' TS]' 
from qryPTGTimesheetsCombinedAllUnionPrepTotalsTIM for xml path('')), 1, 1, ''); 
select @sql += ' FROM qryPreviousStaticQueryThatProvidesRecords GROUP BY Person, Project, [Total P], [Total TS];'; PRINT @sql; exec(@sql);

Answer 3

I think I understand what you're asking and see the problem.

I think the simplest solution - seeing as you're already doing something similar in code already - is to change the ControlSource for the Sum text field. I'm assuming the name of your Summation control is "Sum of Date1P"

In your code, after your script changes the control named "Date1P" to have a control source of "201606 P".

Add line of code

me.[Sum Of Date1P].ControlSource = "=Sum([201606 P])"

Answer 4

If you open a recordset based on the source, you can in VBA reference the fields by index, e.g.:

MyValue = rs.Fields(8).Value + rs.Fields(2).Value / 100