Change Total Value In Query With VBA

[abbaddon223]

Hello Exerts - this one has elluded me for ages!!

I'd like to be able to change the Total Value in a select query using vba from say Avg to Sum

The way I'd like to be able to work it is a from a combo box with

Average
Sum
Count

Then this change the Total value in the query. Is there anyway of doing this?

Thank you for any help in advance

Phil.

 

[pbaldy]

I don't think there's any way of doing it without VBA code. You'd build the SQL for the query in code and concatenate the value from the combo into the SQL string. If you want to change a saved query, you'd use a QueryDef.

 

[StarGrabber]

You're absolutely right, pbaldy!

Dim strFunction As String
    
Select Case [I]yourComboBox[/I].Value
    Case "Count", "Sum"
        strFunction = [I]yourComboBox[/I].Value
    Case "Average"
        strFunction = "Avg"
End Select
    
Dim strQry As String
strQry = "SELECT " & strFunction & "([I]yourTable.yourField[/I]) AS Result FROM [I]yourTable[/I];"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strQry, dbOpenSnapshot)
    
MsgBox [I]yourComboBox[/I].Value & ": " & Nz(rst(0).Value, 0)

rst.Close
set rst = Nothing

 

[abbaddon223]

Hi both,

Thank you for your response - that's pretty cool and worked perfectly!!

At the risk of being a little cheeky - is there a way this could be tied into a chart?

The way I usually do my reporting forms are charts with a query as the control source - so I send to have to do 1 for avg, one for sum etc etc

Is there any way I could get your nifty bit of code to make the value of the chart change - I suppose point the chart at the VBA code or something??

Thanks again - I'm a step forwards already with your help.

 

[StarGrabber]

Sure!

1. Create a new module.

2. Declare a private variable for your combobox value in the module header.

3. Add a Public Sub to the module with the combobox value as parameter in order to fill the variable.

4. Put the code of post # 3 into a new function and adapt it as suitable.

It should look like this:

Option Compare Database
Option Explicit

Private mStrFunction As String

Public Sub SetAggregateFunction( _
    ByVal Aggregate As String)

    Select Case Aggregate
        Case "Count", "Sum"
            mStrFunction = Aggregate
            
        Case "Average"
            mStrFunction = "Avg"
    End Select
    
End Sub

Public Function AggregateQryResult() As Double
On Error GoTo ErrHandler

    If Len(mStrFunction) = 0 Then
        Exit Function
    End If
    
    AggregateQryResult = 0
    
    Dim strQry As String
    strQry = "SELECT " & mStrFunction & "(yourTable.yourField) AS Result FROM yourTable;"

    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(strQry, dbOpenSnapshot)
    
    AggregateQryResult = Nz(rst(0).Value, 0)
    
    rst.Close
    
Exit_Proc:
    Set rst = Nothing
    Exit Function
    
ErrHandler:
    MsgBox "Error " & Err.Number & " in 'AggregateQryResult': " & Err.Description, vbCritical
    Resume Exit_Proc
    
End Function

 

[StarGrabber]

I'm not experienced in charts. After playing around a bit I think you should replace the function by:

Public Function AggregateQry() As String
On Error GoTo ErrHandler

    If Len(mStrFunction) = 0 Then
        Exit Function
    End If
    
    AggregateQry = vbNullString
    
    Dim strQry As String
    strQry = "SELECT Null, " & mStrFunction & "([I]yourTable.yourField[/I]) AS Result FROM [I]yourTable[/I];"
    
    AggregateQry = strQry
    
Exit_Proc:
    Exit Function
    
ErrHandler:
    MsgBox "Error " & Err.Number & " in 'AggregateQry': " & Err.Description, vbCritical
    Resume Exit_Proc
    
End Function

In the chart form you can use this function like this:

yourDiagram.RowSource = yourModule.AggregateQry