Calculated Field Error (1 Viewer)

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