none
How to identify calculated fields in VBA RRS feed

  • Question

  • The VBA code uses the field list  (for each fld in myrecordset.fields...) in a recordset based on a table that selected all fields from the table but needs to do different processing for the regular and for e thcalculated fields.

    Is there a field property that identifies calculated fields?

    for each fld in myrecordset.fields

    if fld.???  then  

    'calculated fields code

    else

    'non calculated fields code

    end if

    Friday, November 25, 2016 6:52 PM

All replies

  • I'm assuming you'd need to check to see if the field has an Expression or not.

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Friday, November 25, 2016 7:41 PM
  • There is no such property. Presumably you know the names of the calculated fields, so check for each one and perform your desired action.
    Friday, November 25, 2016 9:29 PM
  • Yes, there is.

    '---------------------------------------------------------------------------------------
     ' Procedure : ListFieldProps
     ' Author    : Daniel Pineault, CARDA Consultants Inc.
     ' Website   : http://www.cardaconsultants.com
     ' Purpose   :
     ' Copyright : The following may be altered and reused as you wish so long as the
     '             copyright notice is left unchanged (including Author, Website and
     '             Copyright).  It may not be sold/resold or reposted on other sites (links
     '             back to this site are allowed).
     '
     ' Input Variables:
     ' ~~~~~~~~~~~~~~~~
     '
     '
     ' Usage:
     ' ~~~~~~
     '
     '
     ' Revision History:
     ' Rev       Date(yyyy/mm/dd)        Description
     ' **************************************************************************************
     ' 1         2016-11-25              Initial Release
     '---------------------------------------------------------------------------------------
     Function ListFieldProps(ByVal sTbl As String, ByVal sFld As String)
         Dim prp                   As Property
         Dim db                    As DAO.Database
         Dim tdf                   As TableDef
         Dim fld                   As Field
    
        On Error GoTo Error_Handler
    
        Set db = CurrentDb
         Set tdf = db.TableDefs(sTbl)
        Set fld = tdf.Fields(sFld)
    
        With fld
             For Each prp In .Properties
                 Debug.Print prp.Name, prp.Value
             Next
         End With
    
    Error_Handler_Exit:
         On Error Resume Next
         If Not fld Is Nothing Then Set fld = Nothing
         If Not tdf Is Nothing Then Set tdf = Nothing
         If Not db Is Nothing Then Set db = Nothing
         Exit Function
    
    Error_Handler:
         If Err.Number = 3219 Then
             Resume Next
         Else
             MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                    "Error Number: " & Err.Number & vbCrLf & _
                    "Error Source: d" & vbCrLf & _
                    "Error Description: " & Err.Description & _
                    Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                    , vbOKOnly + vbCritical, "An Error has Occured!"
             Resume Error_Handler_Exit
         End If
     End Function

    Obviously not if the field isn't a calculated field, but if you execute it on a Calculated field it does indeed return the value of the Expression property.

    So you could test for the property and check its value, if found it is a calculated field, otherwise not.  So something like:

    '---------------------------------------------------------------------------------------
    ' Procedure : IsFieldCalculated
    ' Author    : Daniel Pineault, CARDA Consultants Inc.
    ' Website   : http://www.cardaconsultants.com
    ' Purpose   : Test a field to determine if it is a Calculated field or not
    '               True -> It is a calculated field
    '               False -> It is not a calculated field
    ' Copyright : The following may be altered and reused as you wish so long as the
    '             copyright notice is left unchanged (including Author, Website and
    '             Copyright).  It may not be sold/resold or reposted on other sites (links
    '             back to this site are allowed).
    '
    ' Input Variables:
    ' ~~~~~~~~~~~~~~~~
    ' sTbl      : Name of the table that contains the field to validate
    ' sFld      : Name of the field to validate
    '
    ' Usage:
    ' ~~~~~~
    ' IsFieldCalculated("tbl_Projects", "ProjName") -> False
    ' IsFieldCalculated("tbl_Projects", "CalFldTest") -> True
    '
    ' Revision History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' **************************************************************************************
    ' 1         2016-11-25              Initial Release
    '---------------------------------------------------------------------------------------
    Function IsFieldCalculated(ByVal sTbl As String, ByVal sFld As String) As Boolean
        Dim db                    As DAO.Database
        Dim tdf                   As TableDef
        Dim fld                   As Field
        Dim prp                   As Property
    
        On Error GoTo Error_Handler
    
        Set db = CurrentDb
        Set tdf = db.TableDefs(sTbl)
        Set fld = tdf.Fields(sFld)
        Set prp = fld.Properties("Expression")
        If Len(prp.Value & vbNullString) > 0 Then
            IsFieldCalculated = True
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not prp Is Nothing Then Set prp = Nothing
        If Not fld Is Nothing Then Set fld = Nothing
        If Not tdf Is Nothing Then Set tdf = Nothing
        If Not db Is Nothing Then Set db = Nothing
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: IsFieldCalculated" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net




    Friday, November 25, 2016 10:36 PM
  • Not all calculated fields have an expression. I use many calculated fields and the calculations are done in code. Your solution would not work for me.
    Monday, November 28, 2016 3:27 PM
  • You changed the definition of "calculated field".

    If you meant "regular old field that I populate using VBA code", then why even ask the question? There is no way Access could know how a regular old field was populated. You will have to maintain meta data about your fields if this is very important to you.

    And as always: 99% of the time calculated fields do not belong in a relational database design, but they should be calculated on the fly in queries.


    -Tom. Microsoft Access MVP

    Monday, November 28, 2016 3:35 PM
  • @AllTheGoodNamesWereTaken

    "Not all calculated fields have an expression. I use many calculated fields and the calculations are done in code. Your solution would not work for me."

    I believe, and they can correct me if I am mistaken, but the question was relating to identifying Calculated Data Types.  How to flag Calculated Fields, thus my answer.

    How could Access be able to identify what you do through code?  In such a case, you'd have to create a table to store a list of table fields in and then you could cross-reference the list to see if the field is "calculated" or not.


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Monday, November 28, 2016 3:49 PM
  • An open recordset cannot tell you if a field is calculated. But you can test to see if a control is calculated rather than a bound field on an open form . I don't know if this will help or not, but here is a little VBA code to test that:

    Dim Source As String
    Source = Forms("YOUR FORM NAME").Controls("YOUR CONTROL NAME").Properties("ControlSource")
    If Left(Source, 1) = "=" Then
        MsgBox (Source)
    End If

    If the controls Control Source leads with an = sign, it is a calculated field on the open form. This could also be reference to a differet control on the same form however, but still "calculated" as such.

    Monday, November 28, 2016 5:52 PM