Expression builder

[mba_110]

Hi

I need your help for query expression builder with iif condition.

Criteria of expression is

if [TotalPoints] is equal to 3.5 then [basic]*.05 or [basic]/2
if [TotalPoints] is above or equal 6 but less then 7 than [basic]*1 or [basic]
if [TotalPoints] is above or equal 7 but less then 8 than [basic]*1.5
if [TotalPoints] is equal or above 8 then [basic]*2

i am able to do the following but not exactly what i need as per above.

IIf([TotalPoints]<=3.5,[Basic]*0.5,IIf([TotalPoints]<6<6.9,[Basic],IIf([TotalPoints]<7<7.9,[Basic]*1.5,IIf([TotalPoints]>7.9,[Basic]*2))))

There might be some thing i am missing, need your kind help thanks.

 

[June7]

I think your .05 should be .5

Will [TotalPoints] have decimal values or only whole numbers?

What should be used if [TotalPoints] is less than 3.5 or between 3.5 and 6?

[basic] * Switch([TotalPoints]>=8,2, [TotalPoints]>=7,1.5, [TotalPoints]>=6,1, [TotalPoints]=3.5,0.5, True,0)

 

[pbaldy]

The <6<6.9 bit is invalid. The tests are applied in order, so the second test should just be <7, the third <8, etc. presumably it's a typo leaving out 3.5 to 6.

 

[mba_110]

Not giving desired results

 IIf([TotalPoints]>=3.4,0.5,IIf([TotalPoints]>=6,1,IIf([TotalPoints]>=7,1.5,IIf([TotalPoints]>=8,2,0))))

Before 3.5 the result should be zero and same as when its false.

 

[plog]

I would do this with a function. You pass it TotalPoints and Basic, it returns the value you want. You would build it in a module like so:

Public Function get_BasicValue(in_TP, in_B)
  ' multiples in_B by appropriate amount based on in_TP
  
    ret = 0
  ' return value, default is 0 to demonstrate error
  
    If (in_TP = 3.5) Then ret = in_B * 0.5
    If (in_TP >= 6) And (in_TP < 7) Then ret = in_B
    ' ...more tests here
    ' calculates value to return when in_TP meets criteria
   
    get_BasicValue = ret


End Function

Then in a query you would use it like so:

BasicValue = get_BasicValue(TotalPoints, Basic)

As others have pointed out, your logic isn't complete. What happens when TotalPoints is less than 3.5? What happens when it is greater than 3.5 and less than 6?

 

[pbaldy]

Not giving desired results

 IIf([TotalPoints]>=3.4,0.5,IIf([TotalPoints]>=6,1,IIf([TotalPoints]>=7,1.5,IIf([TotalPoints]>=8,2,0))))

Before 3.5 the result should be zero and same as when its false.

In what instance(s) is it giving the wrong value? What does it give vs what should it give? Is the TotalPoints field numeric, or could it be text?