1

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?

TimJ
  • 399
  • 4
  • 18
  • Thanks for everyone's input so far, you've helped confirmed what I already suspected, but as yet no one has answered if there is anyway I can use the NAMES of calculated controls in another calculated control. – TimJ Jun 07 '16 at 10:33

4 Answers4

1

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Doing something like this was my first instinct, however the SQL query currently takes 15-20 seconds to deliver results, and if I had to open a recordset of the results to do this wouldn't this mean that the query would be executed twice? Once as a recordset and once again to populate the fields in the report? – TimJ Jun 07 '16 at 10:25
  • My users would be rage quitting after letting the report "hang" for 30 seconds of loading time... – TimJ Jun 07 '16 at 10:32
1

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])"
dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • This is the 'workaround' I have used for now, however I have 18 dynamic fields to automate the controlsource for each record, as well as the collumn label caption and three "totals" calculation boxes to dynamically set the control source for each collumn. (18 collumns x setting 5 scripted (dynamic) control sources in each collumn = a lot of code...) To say the code is now a little unwieldy is an understatement! – TimJ Jun 07 '16 at 10:28
  • @TimB if you're using a SQL Stored procedure, it would seem easier to change the output fields to fixed named fields that can be used as is in your report. Isn't that the simplest solution? Do you have access to the stored procedure? Can you post that SQL? – dbmitch Jun 07 '16 at 15:40
  • I used the dynamic sql that Ullas suggested here: http://stackoverflow.com/questions/36646883/sql-server-table-transposition-pivot Not sure about fixing the sql output collumn names as it's part of the dynamic sql query (columns are created depending on the records provided to the dynamic query by the previous static query) also it would be impossible to trace any (apparent) errors caused by a difference in the calculation to filter the collumns/records to output from SQL and done in the front end to calculate the dynamic column labels and control sources. Is that the way you'd go? – TimJ Jun 07 '16 at 16:04
  • I don't know what your report looks like, but it appears you need the dynamic fields for titles/grouping on your report, but you need fixed field names for summing up fields. If you want to get around all the coding, the only solution I see is to modify your dynamic SQL to include fixed named fields (as well) – dbmitch Jun 07 '16 at 16:16
  • @TimB - can you post the SQL for your procedure? – dbmitch Jun 07 '16 at 16:19
  • `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]' from qryPreviousStaticQueryThatProvidesRecords for xml path('')), 1, 1, ''); select @sql += ' FROM qryPreviousStaticQueryThatProvidesRecords GROUP BY Person, Project, [Total P], [Total TS];'; PRINT @sql; exec(@sql);` – TimJ Jun 07 '16 at 16:53
  • Do you only want to sum totals from " P" field ? It looks like there's another dynamic field called " TS". Is that used or needed in your report? – dbmitch Jun 07 '16 at 17:02
1

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);
dbmitch
  • 5,361
  • 4
  • 24
  • 38
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])"

dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • Many thanks for going back over this! I'll test it first thing tomorrow morning and report back. – TimJ Jun 08 '16 at 17:04