Limit on calculated fields?

I am not aware of such limit.

You have to be MUCH more specific for us to be able to look into this further. For example:

1. What is the error, verbatim?

2. What is the calculated column definition?

3. What are the datatypes of the underlying column of that definition?

4. What was the registry setting suggestion, verbatim?

Can you post a new database with only this table, and stripped of any PII, to a public location such as a free OneDrive account?

I am not aware of such limit.

You have to be MUCH more specific for us to be able to look into this further. For example:

1. What is the error, verbatim?

2. What is the calculated column definition?

3. What are the datatypes of the underlying column of that definition?

4. What was the registry setting suggestion, verbatim?

Can you post a new database with only this table, and stripped of any PII, to a public location such as a free OneDrive account?

Hello Tom:

I do not have access to the database over the weekend, so here is what I got today when I replicated the process:

1.  "The calculated field can not be created.

Verify that expression 'Left([Client Name], 7)' includes fields that exist in the current table."

I would like to state here that it definitely exists. I changed from Client Name to Client_Name to make sure it is not a space issue. 

2. Calculated column is simply "Left([Client Name], 7)"

3. Client Name is a "short text" field. I am defining the calculated field to be also "short text".

4. "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry".

Stripping the PII would be a monumental task, at least the way I know how. I will work on it when I get a bit more time. One table has 61K records, another 71K.

In fact, what I am sensing is that I may be forcing the limits of what Access can do. As I said in my initial post, when I reduce the number of rows to about 1,000, Access has no problems in creating the calculated field.

Thanks for your help,

Al

Thanks Al, good answers.

It worked just fine for me using A2010 32-bit. My registry has MaxLocksPerFile at the default 9500.

I created a new table with Autonumber PK, and Short Text field [Client Name] that I filled with 80,000 rows of random text (see code below, please try on your machine). Then I created the calculated field. It worked.

Now, before you get the idea I consider this a good idea: I do not. This calculation can be performed in a query. Calculated fields should be rare.

Code follows.

Sub fillCalculated()
    Dim rs              As DAO.Recordset
    Dim lngCount        As Long
    Dim strClientName   As String
    Const MAX_RECS      As Long = 80000

    Set rs = CurrentDb.OpenRecordset("tblCalculated", dbOpenDynaset)
    For lngCount = 1 To MAX_RECS
        rs.AddNew
        rs![Client Name] = GetRandomString(10, 30)
        rs.Update
    Next lngCount
    rs.Close
    Set rs = Nothing
    Debug.Print Time$, "Done"
End Sub

Public Function GetRandomString(ByVal intMinLength As Integer, ByVal intMaxLength As Integer)
    Const STRING_CHARS As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim s As String
    Dim i As Integer
    Dim intLen As Integer
   
    intLen = Int((intMaxLength - intMinLength + 1) * Rnd + intMinLength)
    For i = 1 To intLen
        s = s & Mid$(STRING_CHARS, Int((Len(STRING_CHARS) - 1 + 1) * Rnd + 1), 1)
    Next i
    GetRandomString = s
End Function

Is this table linked to any server? If you using linked tables to SharePoint lists on office 365, then such table changes are restricted to less then 5000 rows. In other words, you "can" make the changes before 5000 rows, but not after.

However for desktop and local tables (or a shared network), then 85,000 rows is a rather small file - thus some additional detail is not clear here that preventing you from adding this additional column that is a "calculated" column.

So unless some server system such as SharePoint is involved, then no such restrictions or limit should exist here.

Regards,

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada