Many many if/then statements or case?

[breakingme10]

We have a truck (#117) that is cheaper in labor then all our other trucks. But the price does differ for each company we bill. (Water haul for lease sites in Oil field industry)



I broke it down like this

If [BillTo]=5 AND [TruckNum]=117 Then 77.00
     else
          93.50
End If
If [BillTo]=4 AND [TruckNum]=117 Then 65.00
     else
          80.00
End If
If [BillTo]=3 AND [TruckNum]=117 Then 78.00
     else
          88.00
End If
If [BillTo]=6 Then 90.00
End If
If [BillTo]=11 Then 1.00
End If
If [BillTo]=15 AND [TruckNum]=117 Then 80.00
End If
If [BillTo]=12 Then 100.00
End If
If [BillTo]=8 AND [TruckNum]=117 Then 80.00
     else
           90.00
End If

That determines the rate charges, on my report i'd like the txt box (unbound) to be autopopulated based on the criteria of the billto company and the trucknum.
Should I do a Case statement or a switch of some sort? I'm really bad with code. Please help me out?

[pbaldy]

Well for starters you can't just say "Then 77.00" It would look more like

If [BillTo]=5 AND [TruckNum]=117 Then 
   Something = 77.00
else
   Something = 93.50
End If

"Something" could be a variable, a form control or whatever you're trying to give that value.

[breakingme10]

no that was just an example of what my situation is, the code at the moment looks like this

If Me.BillTo.Value=5 AND Me.TruckNum.Value=117 Then Me.txtRate.Value =  77.00
     else
         Me.txtRate.Value =  93.50
End If
If Me.BillTo.Value=4 AND Me.TruckNum.Value=117 Then Me.txtRate.Value =  65.00
     else
         Me.txtRate.Value =  80.00
End If
If Me.BillTo.Value=3 AND Me.TruckNum.Value=117 Then Me.txtRate.Value =  78.00
     else
          Me.txtRate.Value = 88.00
End If
If Me.BillTo.Value=6 Then Me.txtRate.Value =  90.00
End If
If Me.BillTo.Value=11 Then Me.txtRate.Value =  1.00
End If
If Me.BillTo.Value=15 AND Me.TruckNum.Value=117 Then Me.txtRate.Value =  80.00
End If
If Me.BillTo.Value=12 Then Me.txtRate.Value =  100.00
End If
If Me.BillTo.Value=8 AND Me.TruckNum.Value=117 Then Me.txtRate.Value =  80.00
     else
          Me.txtRate.Value =  90.00
End If

getting an error "ELSE WITHOUT IF" tho

[pbaldy]

You're mixing the block and one-line formats of an If/Then. Personally I stick with the block format, but if you put the Then on the same line as the If, you don't want the End If.

[breakingme10]

I didn't know that! Thank you!

[pbaldy]

Happy to help!

[ranman256]

These values should really be put in a lookup table..(rate table). then there would be no IF ELSE statements at all.
Your table would link to the lookup table and pull the correct rate depending on Truck#.

[breakingme10]

It still isn't working it is just giving me 90 for every one

[breakingme10]

Your right I didn't even consider that because I was using the field as part of a calculation field. But I can't think of a way to list the columns so that it would work for me?

[breakingme10]

|CompanyName|LaborPrice|LaborIf117| ?

[pbaldy]

I agree about putting things in a table. If you follow the logic of your code, you'll basically always get the result of the last test. You'd want ElseIf statements rather than separate If/Then blocks. As is it runs every test and the last one wins.

[breakingme10]

as i was switching it to else if statements the code was auto correcting to this

Private Sub Report_Current()
If Me.BillTo.Value = 5 And Me.TruckNum.Value = 117 Then Me.txtRate.Value = 77
Else: If Me.BillTo.Value = 5 Then Me.txtRate.Value = 93.5
Else: If Me.BillTo.Value = 4 And Me.TruckNum.Value = 117 Then Me.txtRate.Value = 65
Else: If Me.BillTo.Value = 4 Then Me.txtRate.Value = 80
Else: If Me.BillTo.Value = 3 And Me.TruckNum.Value = 117 Then Me.txtRate.Value = 78
Else: If Me.BillTo.Value = 3 Then Me.txtRate.Value = 88
Else: If Me.BillTo.Value = 6 Then Me.txtRate.Value = 90
Else: If Me.BillTo.Value = 11 Then Me.txtRate.Value = 1
Else: If Me.BillTo.Value = 15 And Me.TruckNum.Value = 117 Then Me.txtRate.Value = 80
Else: If Me.BillTo.Value = 12 Then Me.txtRate.Value = 100
Else: If Me.BillTo.Value = 8 And Me.TruckNum.Value = 117 Then Me.txtRate.Value = 80
Else: If Me.BillTo.Value = 8 Then Me.txtRate.Value = 90
End Sub

but i still get ELSE WITHOUT IF error

[breakingme10]

well i fixed that problem, but now it just changes all values to whichever billto txtbox i've clicked on, my report is grouped by BillTo

[IslandHydro]

Perhaps something like this:

If TruckNum.Value = 117 then

' Is Truck 117

Select Case BillTo.Value

Case 1

Var = 2

Case 2

Var = 3

Case 3

Var = 4

Case else

Var = 4

end select

else

' Isn't Truck 117
Select Case BillTo.Value

Case 1

Var = 3

Case 2

Var = 4

Case 3

Var = 5

Case else

Var = 6

end select

end if

I'm not sure what your solving for (where the result goes), in the above, I solved for Var.