IFF ELSE Statement not showing all IFF Else's

[rwn]

I have an long iff statement to show different values based on data in a field. Oddly, if I have two issues. The result of the IFF will not display a 0. Or if the data field is for example "4000VAC" it will not display 32000 either.
So in the example below, if the selection is "OTHER", 0 will not be shown and if the selection is 4000VAC, 32000 will not be shown either.

The table tanks fields are text and are relational to another table.

Expr3: IIf([tb_Order_Scheduling]![Tank]="64000",7500,IIf([tb_Order_Scheduling]![Tank]="40000",5000,IIf([tb_Order_Scheduling]![Tank]="33500",4000,IIf([tb_Order_Scheduling]![Tank]="24000",3000,IIf([tb_Order_Scheduling]![Tank]="8000",1200,IIf([tb_Order_Scheduling]![Tank]="OTHER",0,IIf([tb_Order_Scheduling]![Tank]="4000VAC",32000)))))))

 

[MajP]

You should use a reference table

tblTankValues
skedTank
tankValue

6400 7500
40000 50000
33500 4000
4000Vac 32000
Other 0

It is a database. Link it your done! Now this is flexible, you can add more without changing code.

Nested iifs is a poor design and not recommended.

 

[rwn]

I don't understand. can you explain a little more in detail?

 

[rwn]

I finally understand what you are referring to.

I am using a reference table called Tanks and it is linked to the main table (via the the Data Type, Lookup Wizard ) and it has two fields: Tank and Size. So if the Tank selected is other the value polulated in the expression should be 0, and IFF the Tank selected is 4000VAC the value popluted inthe expression should be 32000.

However, both do not show and it empty/null.

 

[Golom]

If I understand you correctly, you have defined LookUp fields in your table. See this link for a discussion of the Evils of Lookup fields.

To extend MajP's suggestion ... if you have a table
[tt]
tblTankValues
Tank TankValue

6400 7500
40000 50000
33500 4000
4000Vac 32000
Other 0
[/tt]

Then your SQL would (in part) look like

SELECT [blue]... Some Fields ...,[/blue]
       OS.Tank, TV.TankValue,
       [blue]... More Fields ...[/blue]

From ([tb_Order_Scheduling] As OS INNER JOIN tblTankValues As TV
     ON OS.Tank = TV.Tank)
     [blue]... Other Source Specifications ...[/blue]

This assumes that "tblTankValues" contains a definition for every value of "Tank" in [tb_Order_Scheduling]. If it doesn't then the SQL gets a bit more elaborate.

 

[MajP]

tbOrderScheduling

skedID	Tank	 skedDate
1       6400     1/1/2010
2       Other    1/1/2010
3       40000Vac 1/1/2010
4       33500    1/1/2010
5       40000    1/1/2010
6       6400     1/1/2010
7                1/1/2010
8       Other    1/1/2010
9       40000Vac 1/1/2010
10      33500    1/1/2010

tblRefTankSize

Tank	Size
6400     7500
40000    50000
33500    4000
40000Vac 32000
Other    0

query

SELECT 
 tbOrderScheduling.skedID,
 tbOrderScheduling.Tank, 
 tblRefTankSize.Size
FROM 
 tbOrderScheduling 
LEFT JOIN 
 tblRefTankSize 
ON 
 tbOrderScheduling.Tank = tblRefTankSize.Tank;

skedID	Tank	Size
1       6400     7500
2       Other    0
3       40000Vac 32000
4       33500    4000
5       40000    50000
6       6400     7500
7	
8      Other    0
9      40000Vac 32000
10     33500    4000