iIF clause

  • Thread starter Thread starter peljo via AccessMonster.com
  • Start date Start date
P

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 ?
 
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
Click to expand...
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)
Click to expand...
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 <--
 
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 ?
Click to expand...
 
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.
 
hi Klatuu,
The Switch function works well in this situation. Doing it in memory is much
faster than fetching records from a table:
Click to expand...
I had maintainability in mind...)


mfG
--> stefan <--
 
Back
Top