Calculated Field Error (2 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.