Sum field dependent on its control source (1 Viewer)

[dbuser]

Here is a challenge!

I created a query from one table that had multiple fields. The user wanted to search by date so in the design view of the query I went to the "date" field's criteria box and typed in "Between [Enter Start Date] And [Enter End Date]"

I then linked the query to a report for viewing.

The user then decided he would like to not only search by date but to be able to choose which fields he would like to view in the report.

To do this I created a form from the query and added a row of combo boxes (about seven: could only fit about seven fields on legal paper-landscape) and named the first combo box "cboField1", second combo box "cboField2", and so on.... For each combo box the row source type was a Field List and the row source was the query I made.

I then created a report from the query. In the page header i created seven labels naming them lblField1, lblField2, lblField3.......In the detail section i created seven text boxes naming them tbField1, tbField2...

I then created a module with two functions:
GetReport() and SetReportControls()
------------------------------------------------------------------
Option Compare Database

Option Explicit

Sub GetReport()
On Error GoTo Err_GetReport

'Open report in design view to write properties to objects
DoCmd.OpenReport "MoCo", acDesign

'Read combo box selections and use subroutine to set report object properties
SetReportControls Forms!Form1.cboField1.Value, _
Reports!MoCo.lblField1, Reports!MoCo.tbField1
SetReportControls Forms!Form1.cboField2.Value, _
Reports!MoCo.lblField2, Reports!MoCo.tbField2
...
...
'Close design view without prompting to save changes
DoCmd.Close acReport, "MoCo", acSaveYes

'Open finished report in preview view
DoCmd.OpenReport "MoCo", acPreview

Exit_GetReport:
Exit Sub

Err_GetReport:
MsgBox Err.Description
Resume Exit_GetReport

End Sub

Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)

'Check if selection is 'null'
If IsNull(varFieldName) Then 'Blank out the relevant objects
conLabel.Caption = " "
conTextBox.ControlSource = ""
Else 'Write the selected field name to the appropriate objects
conLabel.Caption = varFieldName
conTextBox.ControlSource = varFieldName
End If
End Sub
----------------------------------------------------------------------
Everything worked fine

Problem:
I then found out the user needed to have sums and averages for certain fields within the specified date range.

I created two rows of text boxes in the report's page footer. One row for sums and the other for averages. Each text box has a unique name.

Since I don't know the order or which field the user will choose i couldn't just insert a sum and average expression in the text boxes. For intance if the first field selected was date I would'nt want a sum. If one of the fields has a percentage data type, I wouldn't want a sum just an average for that field.

I want to be able to sum a field dependent on it control source, or user field selected.

Any thoughts would be greatly appreciated.
Will provide more detail if needed

Thanks

 

[mresann]

Try using a mudule level (private) boolean variable for sum and average, call them mblnSum and mblnAverage.

Then when a user control or source is used, the respective boolean variable can be set to true. In the footer section, test for the boolean value being true to set up the sum or average text boxes.