Calculated Field Error (3 Viewers)

[Peter Hibbs]

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.

 

[The_Doc_Man]

I've looked it up and Google Gemini says the backslash (integer divide) is a legal operator in a calculated field. Therefore I have to ask - is [UnitsInStock] a "natural" field in the same table? I don't think you can reference fields in other tables. Also verify spelling and punctuation of the field name, because Access has to be able to find the field.

You can ask Google this question: "why would a simple formula be unusable in an Access table's calculated field?"

My next question is, what is the data type of [UnitsInStock]?

The question after that is, you appear to be computing how many 20-unit boxes it would take to hold your current stock. If that is your intended question, great! If not, consider an alternative.

 

[Peter Hibbs]

Hi Doc
UnitsInStock is in the same table and it is Number type (Integer).
The spellings are correct (if I use the / character instead, it works fine).
The purpose of the Calculated field is to just create a number between 1 and 8 (approximately) which is then used in a query.
I should have mentioned that I am using Access 2021 although I would assume that this problem exists in earlier versions as well.

So are you saying that you tried this out on a database table and the backslash character works correctly?

Peter.

 

[MajP]

I tested it in the most recent version of Access and get the same issue. Says I cannot use it.

 

[MajP]

I tested FIX and that works. That should give the same results. Mod does not work either.
Also INT works. And since you will always have positive values it is the same as fix, /.

 

[cheekybuddha]

Can you use UDF's in your calculation field?

If so, you could try creating a function with unitsInStock as a parameter.

 

[MajP]

Can you use UDF's in your calculation field?

Table level calculated fields do not support UDFs.

 

[The_Doc_Man]

OK, I didn't test "\" in calculated fields because I had somewhere to go in a rush, so I only asked Google Gemini. Just as is the case for ChatGPT, we appear to have found a case where another AI doesn't always give correct answers. Sorry 'bout that.

 

[Peter Hibbs]

Hi Doc_Man,

No problem. I guess that if Microsoft are saying that the backslash should work in this situation then perhaps somebody here should flag it up to them to see if they can fix it. I'm not holding my breath though!

Peter.

 

[theDBguy]

I'm not in front of a computer right now, but could someone please check if the \ operator is also available in the Expression Service? Just a thought...

 

[tvanstiphout]

I'm not in front of a computer right now, but could someone please check if the \ operator is also available in the Expression Service? Just a thought...

The OP's #1 message says that it is not.

 

[MajP]

I did check it and it is in the expression builder and does not work.

The expression builder is somewhat limited but there are still functions it shows that are not supported.

 

[isladogs]

You can of course use both of those operators in a query calculated field

Anyway, I've just emailed the Access team as follows:

 

[theDBguy]

The OP's #1 message says that it is not.

@tvanstiphout, I didn't see the OP mentioned "expression service" in post #1. I saw the "expression property" was mentioned though. Thanks!

 

[arnelgp]

OK, I didn't test "\" in calculated fields because I had somewhere to go in a rush, so I only asked Google Gemini. Just as is the case for ChatGPT, we appear to have found a case where another AI doesn't always give correct answers. Sorry 'bout that.

poor AI, still to blame.

 

[The_Doc_Man]

poor AI, still to blame.

Still to blame because it is so convenient. And doesn't even try to wiggle out of the blame.

 

[Pat Hartman]

No VBA or UDF functions can be used in calculated fields. You can only use functions/expressions that are available in SQL VBA is simply not available to the db engine.

 

[cheekybuddha]

No VBA or UDF functions can be used in calculated fields. You can only use functions/expressions that are available in SQL VBA is simply not available to the db engine.

Yes, @MajP already said that in Post #7

 

[Ken Sheridan]

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.

What's the problem with using a query? As it would be based on a single table, it's result table will be updatable and can be used wherever you'd use a base table. You can then include a computed column using whatever expression you wish. No need to compromise with a less than satisfactory expression.

 

[Pat Hartman]

Yes, @MajP already said that in Post #7

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.