Calculated Field Error (4 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?