CODEWITHSUNDEEP
vbams-access
NS.X.
NS.X.

Reputation: 2142

Custom functions error: 'the expression cannot be used in a calculated column'

In Access 2010 I am trying to use custom VBA functions in a calculated column. I get 'the expression cannot be used in a calculated column'.

Here are my steps:

  1. Launch Access 2010.
  2. Create a new database "DB".
  3. Create a table "Table1" with a text column "Column1". Create a test row with "hello" in Column1.
  4. On "Create" ribbon, click "Module" at the upper-right corner, which launches VBA editor.
  5. In VBA editor "Project" window, there are two projects "ACWZTOOL" and "DB". Choose "DB" and select "Insert" -> "Module".

  6. Write the following code:

    Public Function TestFunc() As String
    TestFunc = "test"
End Function
  7. "Debug" -> "Compile DB" passed, "Save" and closed VBA editor. Now "Module1" appears in "Modules" tab in the left pane.
  8. In Table1, create a calculated column "Column2", with expression "Len([Column1])", the column works properly with the value in test row being 5.
  9. Change the expression to "TestFunc()", the error pops up.
  10. I tried a few other built-in functions, it seems only the "basic" ones are supported in expression builder. E.g. "InStrRev()" is also not recognized.

Upvotes: 4

Views: 7637

Answers (1)

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

According to this guide, user-defined functions are not allowed in calculated column expressions. The relevant quote is in the "Read It" section:

Be aware that calculated fields cannot call user-defined functions, only built-in functions. In addition, you must supply all parameters for methods that you call, even if the parameters are optional.

Upvotes: 6

Related Questions