iIF clause

[peljo via AccessMonster.com]

Can you help me build the right IIf field in the query with the following
conditions :

The field DDU consissts of :
DDU :[exworks]*2+0,4+0,01

To the above expression i must also add 0,001 if size = 205, etc according
to the following table :


205 0.001
60 0.001
20 0.009
1 1.32
4 0,32
0,5 1,67

However Acces does not accept my query, obvioulsy i have errors :


DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001)

Will you help me ?

 

[Stefan Hoffmann]

hi,

To the above expression i must also add 0,001 if size = 205, etc according
to the following table :
205 0.001
60 0.001
20 0.009
1 1.32
4 0,32
0,5 1,67

Create a table with these two fields [Size] and [Offset]. [Size] is the
primary key.

Add this table to your query as a left join.

However Acces does not accept my query, obvioulsy i have errors :

DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001)

Then it will be:

DDU: [exworks]*2+0,41+Nz([Offset];0)

Otherwise:

DDU: [exworks]*2+0,41+
Iif([size] = 205; 0,001; 0)+
Iif([size] = 60; 0,001; 0)+
...+
Iif([size] = 0.5; 0,001; 0)+


mfG
--> stefan <--

 

[peljo via AccessMonster.com]

Thank you so much !

Can you help me build the right IIf field in the query with the following
conditions :

The field DDU consissts of :
DDU :[exworks]*2+0,4+0,01

To the above expression i must also add 0,001 if size = 205, etc according
to the following table :

205 0.001
60 0.001
20 0.009
1 1.32
4 0,32
0,5 1,67

However Acces does not accept my query, obvioulsy i have errors :

DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001)

Will you help me ?

 

[Klatuu]

The Switch function works well in this situation. Doing it in memory is much
faster than fetching records from a table:

DDU: [exworks]*2+0,4+0,01 +
Nz(switch([exworks]=205,.001,[exworks]=60,.001,[exworks]=20,.009,[exworks]=1,1.32,x=4,.32, [exworks]=.5,1.67),0)

As written, if the value of [exworks] is not in the list, the Switch will
return Null, but using the Nz function changes it to 0.

 

[Stefan Hoffmann]

hi Klatuu,

The Switch function works well in this situation. Doing it in memory is much
faster than fetching records from a table:

I had maintainability in mind...)


mfG
--> stefan <--