Very long IIf expression. Need a function instead (1 Viewer)

MeadowLotusSong

New member
Local time
Today, 01:17
Joined
Jul 16, 2018
Messages
2
I have an 80 option IIf expression in Design View that is too long.
EX:
Code:
IIf([SampleCt]="1","A05",IIf([SampleCt]="2","A06",IIf([SampleCt]="3","A07",IIf([SampleCt]="4","A08",IIf([SampleCt]="5","A09",IIf([SampleCt]="6","A10",IIf([SampleCt]="7","A11",IIf([SampleCt]="8","A12",IIf([SampleCt]="9","B01",IIf([SampleCt]="10","B02",IIf([SampleCt]="11","B04",IIf([SampleCt]="12","B05",IIf([SampleCt]="13","B06",IIf([SampleCt]="14","B07",IIf([SampleCt]="15","B08",IIf([SampleCt]="16","B09","NoType"))))))))
There is more!

This expression is meant to fill another column in the table with alpha numeric info so it doesn't have to be typed.
Could you please suggestion a function and approach to set this up?

Cheers,
MLs
 
Last edited by a moderator:

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:17
Joined
Aug 30, 2003
Messages
36,118
Normally you'd have those associations in a table, which would let you either join to that table in a query or do a simple lookup. Having it hard-coded in a formula or function makes it harder to maintain.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:17
Joined
May 21, 2018
Messages
8,463
You do not need a function you need a table
Code:
SampleCt_ID   Sample_Desc
 1                     A05
 2                     A06
 ....
Then just do a simple join on sampleCt to sampleCt_ID and show the Sample_Desc
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:17
Joined
Aug 30, 2003
Messages
36,118
FYI, I edited your post so it didn't extend out so far to the right.
 

Mark_

Longboard on the internet
Local time
Today, 02:17
Joined
Sep 12, 2017
Messages
2,111
How are you trying to update this table? More importantly, why?

If 1 = "Value", then what you really have is the primary key value for a lookup. As the others have said, add a table for this. Then REMOVE the field that is holding the data you are looking up as it is not really needed.

I would highly recommend against putting the lookup in the table definition.

Let us know if you need help with setting up a lookup.
 

Cronk

Registered User.
Local time
Today, 20:17
Joined
Jul 4, 2013
Messages
2,770
The complex IIf statement is symptomatic of a spreadsheet. Excel has vLookup and hLookup functions which simulate an Access lookup table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:17
Joined
Jan 23, 2006
Messages
15,361
MLS,

Please tell us about about the proposed application/database in simple terms. We are suggesting solutions/implementations for a problem for which we have no context. From responses so far you can see a table is required.
But there may be other factors to consider.
 
Last edited:

Users who are viewing this thread

Top Bottom