Calculated Field Error

[MajP]

I think the correct statement would be even narrower. Calculated fields in a table support a subset of Access SQL functions and operators.

So the \ operator is a Access Sql operator AFAIK, but for some reason not supported.
Supposedly Mod is Access Sql and does not work

The question is what are the native Access Sql Functions, because I cannot find an authoritative list that is correct. According to Chat

Microsoft Access SQL includes a variety of mathematical functions that can be used within queries and expressions. These functions can be broadly categorized as:

1. Arithmetic Operators:

• + (Addition)
• - (Subtraction)
• * (Multiplication)
• / (Division)
• `\` (Integer Division) - Returns only the integer portion of a division.
• ^ (Exponentiation) - Raises a number to a specified power.
• Mod (Modulus) - Returns the remainder of a division operation.

2. Numeric/Mathematical Functions:

• Abs(number): Returns the absolute value of a number.
• Atn(number): Returns the arctangent of a number.
• Cos(number): Returns the cosine of an angle.
• Exp(number): Returns e (the base of natural logarithms) raised to a power.
• Fix(number): Returns the integer portion of a number by truncating the decimal part.
• Int(number): Returns the integer portion of a number by truncating the decimal part. (Similar to Fix but handles negative numbers differently).
• Log(number): Returns the natural logarithm of a number.
• Round(number, [num_decimal_places]): Rounds a number to a specified number of decimal places.
• Sgn(number): Returns an integer indicating the sign of a number (1 for positive, -1 for negative, 0 for zero).
• Sin(number): Returns the sine of an angle.
• Sqr(number): Returns the square root of a number.
• Tan(number): Returns the tangent of an angle.

Not everything available in the expression build is an Access Sql functin. As Pat points out they are often VB native functions.

Access Sql functions AFAIK are executed by the database engine and not in the access application environment. Although these functions may have the same name and signature.

So I think there are
Access Sql Functions executed by database engine
Native vba functions executed by expression service
Udf vba functions executed by access environment
And a subset of Access Sql functions supported in a table calculated field

 

[Peter Hibbs]

OK, in view of the above discussions it seems that
either -

the Calculated form is working correctly and Microsoft has not published a definitive list of operators/functions that are valid or invalid​

or -

the Calculated form is NOT working correctly and Microsoft need to fix it.​

It looks like the ball is in their court (and I think we need to be told)!

 

[isladogs]

OK, in view of the above discussions it seems that
either -

the Calculated form is working correctly and Microsoft has not published a definitive list of operators/functions that are valid or invalid​

or -

the Calculated form is NOT working correctly and Microsoft need to fix it.​

It looks like the ball is in their court (and I think we need to be told)!

Did you see post #13?

 

[Peter Hibbs]

Hi Colin,

Yes I did and thanks for passing it on to the Access team. I am not sure how they deal with this sort of issue, do they just issue an update or do they let you know what they are going to do or do they sit on it for weeks or what? I guess you would know better than anybody what the procedure is in this sort of situation.

Peter.

 

[isladogs]

The speed of response varies according to how significant the effects are, the number of users affected & whether acceptable workarounds are available. Some bugs get immediate attention. Other items may take longer and there are some known bugs that have existed for years and will likely never be fixed.

I reported two issues today & got a response to both from members of the Access team within a few hours.

From the initial response received in this case, I can confirm that it isn't a recent regression.
There are two relevant help articles but neither has the specific info that you would like:
Learn to build an expression - Microsoft Support
Use the Expression Builder - Microsoft Support

It is possible that at some point, the documentation will be made more specific and that some additional functionality will be added in terms of usage at the table level. However, I very much doubt doing that will be high on the A-team's priority list

In the meantime, you do have alternatives, both to the initial integer division at table level or to do this calculation at query level (which personally I would recommend)

 

[LarryE]

What is a calculated field data type supposed to be used for? I have never used one.

 

[The_Doc_Man]

What is a calculated field data type supposed to be used for? I have never used one.

It puts a calculated value in a table's field (as opposed to doing it in a query's field or a form's unbound control). There are some pros and cons to using it in table context because of limitations on when it is actually computed. You also have limits on the fields that can be used in its definition AND there are limitations on the formula you can use. If you understand the context of the phrase "Quick and Dirty" then you have sufficient understanding of why they are used as opposed to query or form computations.

 

[LarryE]

It puts a calculated value in a table's field (as opposed to doing it in a query's field or a form's unbound control). There are some pros and cons to using it in table context because of limitations on when it is actually computed. You also have limits on the fields that can be used in its definition AND there are limitations on the formula you can use. If you understand the context of the phrase "Quick and Dirty" then you have sufficient understanding of why they are used as opposed to query or form computations.

I'll steer clear thanks.

 

[Minty]

It can be very useful for simple things to avoid creating them in a query.
One of the best examples would be concatenating FirstName and LastName fields into a calculated FullName field , also Initials , as they are frequently used.

 

[MajP]

What is a calculated field data type supposed to be used for?

My guess is that this was may have been added to keep compatibility with Sharepoint. These came out the same time attachment fields, and multi value fields came out. These features all seem kind of superfluous in Access and not well adopted. In Access you have queries and subforms but those features do not exist in SharePoint. Calculated fields and multivalue fields are the only option for Sharepoint to have a UI for this kind of data. Instead of subforms SP shows child records in an MVF. Instead of calculated fields in queries SP provides that in a table ("list").

 

[cheekybuddha]

That is too subtle a leap for most novices.

I can assure you @Peter Hibbs is no novice!

 

[Mark_]

Hi All,

I have a Calculated type field in a table (and I am aware that these are not normally recommended but this is only for a small demo program and this method is the easiest option for my purposes) which fails when I enter the calculation formula.

Anyway, I entered the following ([UnitsInStock]+20)\20 in the Expression property and it immediately came up with the error message:- "The expression ([UnitsInStock]+20)\20 cannot be used in a Calculated column." After a bit of experimenting it seems that the Backslash character (Integer Divide) is not allowed in this situation. I guess there is no way around this restriction so I have opted for the / character instead and set the Result Type property to Integer which is good enough for this application.

I seem to remember that the Integer Divide function was added to the VBA code a few years ago but obviously the Access Team did not see fit to update the tables as well, maybe they could be persuaded to do that some time!

If anyone has any other insight regarding this issue then I would be interested to hear it.

Peter Hibbs.

If you are using a form for data entry, you could avoid doing the calculation in the table and do it in a field on screen, then STORE the result. If you've not resolved this yet, that may be quicker for your demo than spending more time trying to work this out.

 

[Mark_]

As long as you understand why we don't store calculated values as normal practice.

He admitted in his first post he knows, but this is for a demo. I'm just hoping he's able to get it done in the timeline he needs.

Having had to work through an issue or two with clients in the distant past, I can see wanting to be able to edit a "Calculation" while with them, especially if they are having difficulty articulating a requirement. Ask what should be shown, show what they ask for, then check if it matches what they were expecting. Some people know what the end results SHOULD be, but can't write out a math equation to save their cloaca.

 

[xavier.batlle]

I believe the only plus is that they can be indexed so if you need to do a lot of searching on a calculated value, the index could be helpful.

No, if you create a calculated field directly in a table (using the "Calculated" data type), Access does not allow you to index it.

 

[The_Doc_Man]

Now, Pat... given the abominations we have both seen on this forum in over 20 years, you know there is ALWAYS a reason. It just doesn't have to be a good one.

Past a certain point, I become amused by the logic some people offer for the crazy, inappropriate, or disastrous things they do. And then I think back to times when I didn't know better and, before I found this forum, had to guess what to do. Wrong is still wrong, but thinking back to when I was a raw beginner, I have learned to forgive others for failings I once shared (and still might).

 

[Cotswold]

I have been using Access since the last century and have never used a calculated field in a table.
Mainly because I've had the impression, rightly or wrongly, that I'm not totally in control of them.

 

[CJ_London]

Providing you understand the limitations I don’t see a problem with a calculated field-they can’t be indexed, but then the same calculation in a query can’t be either.

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

 

[The_Doc_Man]

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

As it happens, that question came up for one of my current projects - the genealogy DB I've built for my family, now with over 2000 people listed - because I'm doing my own family, my wife's family, and her first husband's family - so that my grandsons (technically, step-grands) will know about their roots and influencers.

The "concatenate names" thing came up so often that I built a special function for it, and it is used both in VBA generation of reports and in queries that are used on forms and reports. The basics are to remove excessive spaces in case the middle name is missing, because that led to too many spaces in some names, totally screwing up the appearance of the names in some VBA-generated Word documents that I produce.

 

[xavier.batlle]

Providing you understand the limitations I don’t see a problem with a calculated field-they can’t be indexed, but then the same calculation in a query can’t be either.

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

I totaly agree with you. Sometimes, when I have needed a calculated field that should be indexed, or that it can't be set with the current operators, I use data macros to simulate it. It has been very useful in some projects.

 

[Ken Sheridan]

The "concatenate names" thing came up so often that I built a special function for it..............

I use the following generic function for names and any other sets of values which require concatenating. It was originally adapted from a CanShrinkLines function published by Microsoft many years ago for returning an address, suppressing Null lines:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName, MiddleName,LastName)
    
    Dim X As Integer, strLine As String
    
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
      
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

The following query is an example of its use, firstly to concatenate a contact's names, and secondly their address. The query is the RowSource property of a combo box in whose list the address is returned in a second column to differentiate two or more people of the same name:

SELECT
    Contacts.ContactID,
    ConcatValues (
        " ",
        FirstName,
        MiddleName,
        LastName
    ),
    ConcatValues (
        ", ",
        AddressLine1,
        AddressLine2,
        City,
        Region,
        PostCode
    )
FROM
    Regions
    INNER JOIN (
        Cities
        INNER JOIN Contacts ON Cities.CityID = Contacts.CityID
    ) ON Regions.RegionID = Cities.RegionID
ORDER BY
    Contacts.LastName,
    Contacts.FirstName;