Creating a field with/without calculation depending on other field

[papavictorlima]

Hi all,



Think the title makes sound it more difficult than it is, but I cant find the solution
Right, Access 2016 .... Table fields:
- number (containing various numbers)
- a/b (containing either a or b)
- NEWnumber

I want [NEWnumber] to copy over the values from [number] when [a/b] = a
I want [NEWnumber] to copy over the values from [number] * 2 when [a/b] = b
Also if possible, new number should not contain anything > 750 and < 1 (meaning also ruling out 0 and negative numbers)

Although the setup is slightly different, if the above is possible, I can implement this in my table.
With Access 2016 I would think this is able to be done within the table and no additional query is needed?
I looked everywhere for several statements like IIF etc etc but haven't found the solution.
Any one have an idea how this can be accomplished??

MANY thanks

[CJ_London]

I'm assuming your real field names are valid - the ones you have provided are not and may cause an issue

I don't use calculated fields but as a quick test this works

iif([number]>0 and [number]<750,[number]*IIf([a/b]="a",1,2),0)

I looked everywhere for several statements like IIF etc etc but haven't found the solution.

which I did using the expression builder, so not sure why you had a problem.

For calculated fields you can only reference fields in the same table (and I don't think you can reference another calculated field) plus there are limited functions you can use - but iif is OK

[papavictorlima]

Thanks Ajax
Of course the given info was not real
Basically I have a field [distance driven] which can be miles or kilometres
Hence the field [k/m]
So [miles driven] should be a copy of [distance driven] if [k/m] = m
And the calculation from km to miles if [k/m] is k

Your statement works , just need to get my head around the last bit of your answer
Although it works, I just want to grasp the technique...
"IIf([a/b]="a",1,2),0)" , Can you explain what I read here?
I think b=0 meaning no change
Just dont understand the "a",1,2
As said, it does work, just trying now to understand what is happening

[papavictorlima]

Sorry for the last post, after some playing I see the effects and DO understand the statements
The only thing I can't figure out is how to NOT populate the field if > 750 and < 1 in stead of having 0.
Although the end result could be done with a query, but not populating it at all, makes it one step neater

[CJ_London]

I misread this

Also if possible, new number should not contain anything > 750 and < 1

, thought you were saying if the number is 'out of range'

not sure if it will work but try this instead

iif([number]*IIf([a/b]="a",1,2)>750 OR [number]*IIf([a/b]="a",1,2)<0,null,[number]*IIf([a/b]="a",1,2))

I don't know about neater, you cannot index a calculated field so any queries based on it will consequently be slow. But OK for relatively small numbers of records, the difference will not be really noticeable.