Access 2013 calculated field too complex

Question

Apparently, calculated field is way to complex. Could anyone suggest a better way to accomplish what I'm trying to do?

Goal: The value entered in the Strength Field determines that value that appears in the calculated field. Thanks in advance for any help!

[Strength] number field [StrMod] Calculated field

Below is the expression I tried to build to support the [StrMod] calculated field.

IIf([Strength]=1,-5,
IIf([Strength]>=2 And [Strength]<=3,-4,
IIf([Strength]>=4 And [Strength]<=5,-3,
IIf([Strength]>=6 And [Strength]<=7,-2,
IIf([Strength]>=8 And [Strength]<=9,-1,
IIf([Strength]>=10 And [Strength]<=11,0,
IIf([Strength]>=12 And [Strength]<=13,1,
IIf([Strength]>=14 And [Strength]<=15,2,
IIf([Strength]>=16 And [Strength]<=17,3,
IIf([Strength]>=18 And [Strength]<=19,4,
IIf([Strength]>=20 And [Strength]<=21,5,
IIf([Strength]>=22 And [Strength]<=23,6,
IIf([Strength]>=24 And [Strength]<=25,7,
IIf([Strength]>=26 And [Strength]<=27,8,
IIf([Strength]>=28 And [Strength]<=29,9,
IIf([Strength]=30,10,Null)

Answer 1

Here are a few suggestions all using a non-calculated data field type.

UPDATE QUERY w/ LOOKUP TABLE

Replace the calculated field type to regular number field for [StrMod]. Create a Strength lookup table:

StrengthValue  | StrengthCategory 
1                 -5 
2                 -4 
3                 -4
4                 -3  
5                 -3
6                 -2
...               ...

Then use this table to create the below update query to be run in the AfterUpdate and AfterInsert data macros for main table or same events in main table's form.

UPDATE maintableName INNER JOIN StrengthLookUp 
       ON maintableName.Strength = StrengthLookup.StrengthValue 
SET maintableName.StrMod = StrengthLookUp.StrengthCategory

UPDATE QUERY w/o LOOKUP TABLE

Replace the calculated field type to regular number field for [StrMod] and simply use an update query in AfterUpdate and AfterEvent events:

UPDATE maintableName 
SET maintableName.StrMod =    
    IIf([Strength]=1,-5,
    IIf([Strength]>=2 And [Strength]<=3,-4,
    IIf([Strength]>=4 And [Strength]<=5,-3,
    IIf([Strength]>=6 And [Strength]<=7,-2,
    IIf([Strength]>=8 And [Strength]<=9,-1,
    IIf([Strength]>=10 And [Strength]<=11,0,
    IIf([Strength]>=12 And [Strength]<=13,1,
    IIf([Strength]>=14 And [Strength]<=15,2,
    IIf([Strength]>=16 And [Strength]<=17,3,
    IIf([Strength]>=18 And [Strength]<=19,4,
    IIf([Strength]>=20 And [Strength]<=21,5,
    IIf([Strength]>=22 And [Strength]<=23,6,
    IIf([Strength]>=24 And [Strength]<=25,7,
    IIf([Strength]>=26 And [Strength]<=27,8,
    IIf([Strength]>=28 And [Strength]<=29,9,
    IIf([Strength]=30,10,Null))))))))))))))))

VBA LOGIC

Replace the calculated field type to regular number field for [StrMod]. Then, use the SELECT CASE statement in the main table's form's AfterInsert and AfterUpdate events:

SELECT CASE Me.Strength

    Case 1
    Me.StrMod = -5

    Case 2 To 3
    Me.StrMod = -4

    Case 4 To 5
    Me.StrMod = -3

    Case 6 To 7
    Me.StrMod = -3

    Case 8 To 9
    Me.StrMod = -1

    Case 10 To 11
    Me.StrMod = 0

    Case 12 To 13
    Me.StrMod = 1

    ...

END SELECT

Strictly my preference, but I never work with calculated fields in case of database compatibility (i.e., MS Access 2007 accdb users) and upsizing scalability with programming languages (PHP, Python, VB ODBC connections) and other RDMS (SQL Server, MySQL).

Answer 2

I can not check but you will get the idea:

iif(strength/2 >= 15, null, -5 + INT(strength/2))

Did you want to simplify or complicate? If you want just to update your table once then you don't need any lookup tables...

update table 
set StrMode = iif(strength/2 >= 15, null, -5 + INT(strength/2))

But if records are added to table from time to time you will need to run update as well.