permissions problem with built-in functions in calculated fields

[Cathy Artigues]

Hi - I'm sure there's something basic that I'm missing with regard to an Access security setting for built in functions, but I'm stumped and hope someone can point me in the right direction. The only thing I've been able to find is a suggestion to run Compact & Repair, which I have done with no effect. Also, I reproduced this on a brand new empty database as follows.

Create a simple database with one table (Table1).

Fields:
* ID is the auto-generated key
* Field1 is short text
* Field2 is a calculated short text field with the expression: [Field1] & Left$("xxx",1)

If I manually type data into the database, then Field2 calculates and fills correctly.

Using the ucanaccess console, I open the database and insert one record. Data in Field1 is updated, but Field2 remains blank. Here's the console log:

Please, enter the full path to the access file (.mdb or .accdb): database1.accdb
WARNING:user lacks privilege or object not found: LEFT$
WARNING:user lacks privilege or object not found: LEFT$
Loaded Tables:
Table1
Loaded Queries:

Loaded Procedures:

Loaded Indexes:
Primary Key on Table1 Columns: (ID)

UCanAccess>
Copyright (c) 2017 Marco Amadei
UCanAccess version 4.0.1
You are connected!!
Type quit to exit

Commands end with ;

use:
export <pathtocsv>;
for exporting the result set from the last executed query into a .csv file</pathtocsv>

UCanAccess>insert into table1 (field1) values('bbb');
UCanAccess>1 row(s) affected

UCanAccess>

If I change the function to Left() (returns variant instead of string), then the warning changes to "WARNING:incompatible data type in conversion".

[Gord Thompson]

No, I don't think you're missing anything.

From what I can see, Left$() simply has not been implemented. However, Left() seems to work okay in queries (both interactive and saved in Access) but not in a calculated field. (A simple calculated field like [Field1] & "xyz" works okay.)

[Cathy Artigues]

Thanks for looking at it. I don't know if these messages can be clarified somehow, but it seems like it should be more than just a warning if you're trying to insert to a record that has a calculated field.

I have a workaround for my issue. I need to add the calculated column to a very large table. Adding it in Access automatically filled in this data for all existing records, so the problem now is only on new records. I can change the field type back to a simple short text field and modify the Java code that's executing the insert to do the calculation and just include it in the inserted data.

[Marco Amadei]

Hi Cathy and Gord, it looks like a regression, with Left() it should work. I'll take a look ASAP.
In general functions work in calculated fields...

[Cathy Artigues]

Hi Marco -- in case it helps pinpoint when the regression occurred, I originally discovered the problem in v3.0.6 and upgraded to v4.0.1 to see if that solved it. I just double checked and Left() is definitely failing on insert in 3.0.6.

[Marco Amadei]

Oh yes, it wasn't actually a regression. Left$ isn't implemented and Left is implemented by hsqldb. Overriding the hsqldb implementation and implementing Left$ (after having solved some naming issue) it works. It will be in the 4.0.2.