Assistance - Issue with Expression Function in SQL Query and Invoking Function in VBA Editor

[Zikar]

Hello ACCESSWorld Gurus, out there,

This is my first posted thread on this great forum. I am looking forward to receiving advice or direction from anyone who can identify and assist in resolving the issue that I am having with the development of a somehow very complex procedure in my MS Access Database.

The issue I am having is in creating an expression for a Public Function in a SQL query which I am calling from a VBA code module. To make it easy to understand what the issue is, I am providing the following details that explain the developed procedure and also an example which I did and attached to this thread as an Excel file. The example demonstrates the exact output results that I expect when I would run the SQL query. The following are the details:

1. I wrote a SQL query by the name ‘qry_QA_CONC_MovingStdDev’ which brings data from a Table with name ‘tbl_QA_CONC_Concrete_AnalysisData’. This Table has the following control fields described by their type:

- [ID_Record] – Type AutoNumber - Long Integer

- [ID_MixCode] – Number - Long Integer

- [ConcretePourDate] – Date/Time - Short Date

- [CompressiveStrength(MPa)] - Number – Double

The following is the SQL query that I created to return the values of a calculated moving standard deviation (refer step 2. below) by an Expression field with the name ‘MovingStdDev’:

SELECT tbl_QA_CONC_Concrete_MixDesignData.ID_MixCodeDesignRecord, tbl_QA_CONC_Concrete_MixDesignData.MixCode, tbl_QA_CONC_Concrete_AnalysisData.ConcretePourDate, Format([ConcretePourDate],"yyyy-mm") AS PourMonthYear, tbl_QA_CONC_Concrete_AnalysisData.[CompressiveStrength(MPa)], tbl_QA_CONC_Concrete_MixDesignData.NominatedStdDev, Round([NominatedStdDev],2)*1.29 AS [Upper Limit StdDev], CalculateMovingStdDev([MixCode],[PourMonthYear]) AS MovingStdDev
FROM tbl_QA_CONC_Concrete_AnalysisData LEFT JOIN tbl_QA_CONC_Concrete_MixDesignData ON tbl_QA_CONC_Concrete_AnalysisData.ID_MixCode = tbl_QA_CONC_Concrete_MixDesignData.ID_MixCodeDesignRecord
ORDER BY tbl_QA_CONC_Concrete_MixDesignData.ID_MixCodeDesignRecord, tbl_QA_CONC_Concrete_AnalysisData.ConcretePourDate, Format([ConcretePourDate],"yyyy-mm");

2. I developed a VBA code as an approach to calculate a moving standard deviation which I refer to in the VBA code as MovingStdDev. The calculation of MovingStdDev is based on the data from the above SQL query. The procedure in the VBA module calculates the moving standard deviation MovingStdDev of the ‘CompressiveStrength(MPa)’ field that must be partitioned by the ‘Text’ value of the field ‘MixCode’ and the expression field ‘PourMonthYear’ which is a ‘Date’ type “yyyy-mm”, and ordered by the ‘Date’ type field ‘ConcretePourDate’ on the basis that the count is equal or greater than 10 rows and the calculation of the standard deviation starts for ROWs BETWEEN 9 PRECEDING AND CURRENT ROW. In other words, the calculation of MovingStdDev is activated at the tenth row of the data range for the specific partition. Please see the example I provided in the attached Excel sheet.

The VBA code module is as follows:

Option Compare Database

Public Function CalculateMovingStdDev(MixCode As String, PourMonthYear As Date) As Double
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strSQLInner As String
    Dim strSQLFinal As String
    Dim MixCodeValue As String ' Declare MixCode here
    Dim PourMonthYearValue As Date ' Declare PourMonthYear here
   
    ' Set the SQL query to fetch the data
    strSQL = "SELECT qry_QA_CONC_PourMonthYear.* " & _
             "FROM qry_QA_CONC_PourMonthYear;"
   
    ' Initialize the database and open the recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
   
    ' Loop through the recordset and calculate the moving standard deviation
    Do While Not rs.EOF
        MixCodeValue = rs("MixCode") ' Assign value to MixCodeValue
        PourMonthYearValue = rs("PourMonthYear") ' Assign value to PourMonthYearValue
       
        ' Build an inner SQL query to calculate the moving standard deviation
        strSQLInner = "SELECT TOP 10 [CompressiveStrength(MPa)] " & _
                      "FROM qry_QA_CONC_PourMonthYear " & _
                      "WHERE MixCode='" & MixCodeValue & "' " & _
                      "AND PourMonthYear<='" & PourMonthYearValue & "' " & _
                      "ORDER BY PourMonthYear DESC;"
       
        ' Execute the inner query and calculate the standard deviation
        Dim rsInner As DAO.Recordset ' Use a separate recordset for the inner query
        Set rsInner = db.OpenRecordset(strSQLInner)
       
        Dim DataArray() As Double
        Dim i As Integer
        ReDim DataArray(1 To 10)
       
        i = 1
        Do While Not rsInner.EOF And i <= 10 ' Ensure we don't exceed the array bounds
            DataArray(i) = rsInner("CompressiveStrength(MPa)")
            i = i + 1
            rsInner.MoveNext
        Loop
       
        rsInner.Close
       
        ' Calculate the standard deviation using the new method
        MovingStdDev = NewStdDev(DataArray, i) ' Pass the number of elements in the array
       
        ' Do something with the MovingStdDev value for each row
        Debug.Print "MixCode: " & MixCodeValue & ", PourMonthYear: " & PourMonthYearValue & ", MovingStdDev: " & MovingStdDev
       
        rs.MoveNext
    Loop
   
    ' Clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

' Function to calculate the standard deviation of an array of values using Decimal data type
Private Function NewStdDev(DataArray() As Double, n As Integer) As Double
    If n < 2 Then
        ' Handle the case when n is less than 2
        NewStdDev = 0
    Else
        Dim sum As Double
        Dim sumSquares As Double
        Dim mean As Double
        Dim diff As Double
        Dim variance As Double
       
        sum = 0
        sumSquares = 0
       
        For i = 1 To n
            If i >= LBound(DataArray) And i <= UBound(DataArray) Then ' Check array bounds
                sum = sum + DataArray(i)
            End If
        Next i
       
        mean = sum / n
       
        For i = 1 To n
            If i >= LBound(DataArray) And i <= UBound(DataArray) Then ' Check array bounds
                diff = DataArray(i) - mean
                sumSquares = sumSquares + diff * diff
            End If
        Next i
       
        variance = sumSquares / (n - 1)
       
        NewStdDev = Sqr(variance)
    End If
End Function

3. I compiled the Database and debugged the above VBA code which resolved all compilation errors that existed.

4. I designed a macro named ‘PrintMovingStdDevPreview’ with the following actions step by step:

a. OpenQuery Action:

Action: OpenQuery

Query Name: qry_QA_CONC_MovingStdDev

View: Datasheet

b. PrintPreview Action:

Action: PrintPreview

5. The following captured screen shows the available references:

THE ISSUE

When I run the SQL query ‘qry_QA_CONC_MovingStdDev’, the macro ‘PrintMovingStdDevPreview’, or invoke the ‘CalculateMovingStdDev’ function in the VBA editor, I get for any of those actions the Microsoft Access error message Undefined function ‘CalculateMovingStdDev’ in expression. Showing in the following captured screen.

I am finding difficulty to determine the problem with the Expression that I have set in a column of the SQL query which I showed in the above step 1. The SQL query perfectly runs when I remove the expression that is supposed to return the values for the calculated moving standard deviation. I would greatly appreciate if someone can provide a clear direction on how to resolve the above issue. All what it wanted is to have the SQL query run and generate the calculated standard deviation by an expression function in a column similar to that shown in the attached Excel sheet example.

 

[MajP]

Normally I see that error when
1. You have a typo in the name in the query: example calculateMovingStdev
I do not see one.
2. You mistakenly made the function Private not Public
Do not see that
3. You put the function in a Forms, report, or class module
I cannot tell if this is an issue.
4. You need to compile your code because there is actually another issue blocking the function call

 

[pbaldy]

5. You name the module the same as the function.

 

[ebs17]

Public Function CalculateMovingStdDev(MixCode As String, PourMonthYear As Date) As Double
' ...
   CalculateMovingStdDev = ...       ' ???
End Function

I don't see any return from the function.

 

[June7]

Without explicit return, function should return 0 to all records because it is declared as a Double. I tested with a simple function and it does not error. So something else is going on. Considering the error message, most likely cause is # 5.

I used to work for state materials lab and dealt with mix designs. I built Access database for tracking and reporting results of many test procedures.