Calculated Field Error (3 Viewers)

[cheekybuddha]

Thank you for your contribution. Did he also say that VBA functions are not allowed? The only functions that you can use are those directly supported by SQL.

Sorry, I wasn't aware that you could create a UDF without VBA.

 

[Peter Hibbs]

Hi Pat,

The only functions that you can use are those directly supported by SQL.

What exactly do you mean by SQL? Do you mean that Integer Divide and Mod cannot be used in a Query (which they can) or did you mean something else? I am a bit confused.

Hi Ken,
Yes, I could use a query but using this method was simpler and for my purposes was adequate for the task. If I was to use this code in a 'proper' application then I would probably use a query but it did not seem necessary in this instance.

Peter.

 

[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.

 

[Pat Hartman]

Sorry, I wasn't aware that you could create a UDF without VBA.

Not being able to use a UDF is not the same thing as not having VBA available. That is too subtle a leap for most novices.

 

[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").

 

[Pat Hartman]

In the best situations, calculated fields have very limited usefulness. The calculation has to be straight SQL. It cannot involve VBA at all. It also can only use hard-coded values or values from other fields in the current record. 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.

 

[cheekybuddha]

That is too subtle a leap for most novices.

I can assure you @Peter Hibbs is no novice!

 

[Pat Hartman]

I can assure you @Peter Hibbs is no novice!

Since you seem to want to be right for some reason. Ok, you're right.

 

[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.

 

[Pat Hartman]

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.

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