help with conditonal expression

[kimmole]

help with conditonal expression
how do i?......


field a
b
c
d
e
f
g
h
i

i need to make a conditional calculation.... on fields h and i

...h= if g-c= or <0 then h=0
if g-c>0 and <e then h=((g-c)/100)*d
if g-c= or >e then h=(e/100)*d

...i= if g-c<e then i=0
if g-c>e then (((g-c)-e)/100)*f

can anyone form thes two onto the proper syntax for each field? ....pretty please!!!!

 

[ssecca]

? Are you doing this on a form, in a query or in VBA?

ssecca

 

[sdk]

Hi,

This will calculate the values of h & i for a query, but will not store them. If you must store them you will need to do some code, best using Select..Case statements behind a form. That would be a quicker solution than this (iif is slow and that is compounded by nested statements), and probably more robust.

SELECT a, b, c, d, e, f, g, IIf([g]-[c]<=0,0,IIf([g]-[c]>0 And [g]-[c]<[e],(([g]-[c])/100)*[d],((([g]-[c])-[e])/100*[f]))) AS h, IIf([g]-[c]<[e],0,((([g]-[c])-[e])/100*[f])) AS i
FROM yourTable;[\color green]

Cheers

Steve

 

[mingis]

Enter these two functions to any module:

Function Calc_h(c As Double, d As Double, e As Double, g As Double)
Dim h As Double
h = 0
If (g - c <= 0) Then h = 0
If ((g - c > 0) And (g - c < e)) Then h = ((g - c) / 100) * d
If (g - c >= e) Then h = (e / 100) * d
Calc_h = h
End Function

Function Calc_i(c As Double, e As Double, f As Double, g As Double)
Dim i As Double
i = 0
If (g - c <= e) Then i = 0
If (g - c > e) Then i = (((g - c) - e) / 100) * f
Calc_i = i
End Function

In the query use expressions Calc_h([c], [d], [e], [g]) for h and Calc_i([c], [e], [f], [g]) for i.