I've what seems a trivial thing to do in MS Access 2016, but can't get it right.

I have a table named "Values", and I want to get the Length of a column named Value_Name which is of data type "short text"

So I click Create > Query Design. I show table "Values". I double click column "Value_Name" so it's the first field. I click in the second column, and for a Field value I enter:

Length of value: Len(Value_Name)

If I click anywhere else, I get a pop-up error saying "There was an error compiling this function. The Visual Basic module contains a syntax error." When I close the error it highlights "Len" in my Field.

I've tried with several functions (replace, left, etc) and can't get any to not give the above error, even when adding the column with the "Build..." right click menu.

So I edit the SQL directly, and enter:

SELECT Len("Test") AS TestLength;

And this gets "Compile error. in query expression 'Len("Test"'.

... and yes, the error message has a period after the word "error" for some reason.

CodePudding user response：

In queries specifically, this issue occurs when VBA has a problem compiling some code.

As June7 hinted, it may be caused by a missing reference, but only if there's some VBA code failing to compile. It can be code with plain syntax errors too.

To track down this code, enter the VBA editor (Alt F11) and hit Debug -> Compile Database. The editor should point you close to the code that's failing to compile, which you'll have to fix.