Solved Evaluating conditions in MS Access with VBA

[nector]

I'm trying to evaluate four conditions in Ms Access in such a way that tax must be charged on the higher price not the lower, but I keep on get one way evaluation even if I try to lower the other figures to see if there will be a change in calculations nothing happens:

VBA Code Affected

If (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) > ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)

ElseIf (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) < ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

If (Me.TaxClassA = "B") And ((((Me.RRP) / 1.16) * Me.Quantity) > (((Me.UnitPrice) / 1.16) * Me.Quantity)) Then
Me.Taxable = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)
ElseIf (Me.TaxClassA = "B") And ((((Me.RRP) / 1.16) * Me.Quantity) < (((Me.UnitPrice) / 1.16) * Me.Quantity)) Then
Me.Taxable = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

See if the unfunctional link can be found, the code only evaluate the first , the ElseIF condition is ignored why?

 

[cheekybuddha]

When would this expression:

((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) > ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then

yield a different result from:

Me.RRP > Me.UnitPrice

 

[cheekybuddha]

As I see it (and I could well be wrong!), both If statements above reduce to the same:

If Me.TaxClassA = "B"
And Me.RRP > Me.UnitPrice Then
  Me.Taxamount = (Me.RRP / 1.16) * Me.Quantity * 0.16
Else
  Me.Taxamount =(Me.UnitPrice / 1.16) * Me.Quantity * 0.16
Else
  ' Me.RRP = Me.UnitPrice
  ' OR Me.TaxClassA <> "B"
End If

What, in English, are you trying to achieve?

 

[nector]

The result that must be archived is that , if RRP is higher UnitPrice then return : (Me.RRP / 1.16) * Me.Quantity * 0.16

Otherwise RRP < UnitPrice then return : Me.Taxamount =(Me.UnitPrice / 1.16) * Me.Quantity * 0.16

 

[cheekybuddha]

OK, would this work for you:

If Me.TaxClassA = "B" Then
  Me.Taxamount = (IIf(Me.RRP > Me.UnitPrice, Me.RRP, Me.UnitPrice) / 1.16) * Me.Quantity * 0.16
Else
  ' What happens for TaxClassA <> "B"
End If

 

[cheekybuddha]

Will you ever need to handle Nulls?

If Me.TaxClassA = "B" Then
  Me.Taxamount = (IIf(Nz(Me.RRP, 0) > Nz(Me.UnitPrice, 0), Nz(Me.RRP, 0), Nz(Me.UnitPrice, 0)) / 1.16) * Nz(Me.Quantity, 0) * 0.16
Else
  ' What happens for TaxClassA <> "B"
End If

 

[nector]

My apologies people I'm sorry the original code actually works , I forgot that it was on after quantities updates, however, many thanks to all the contributors.

If (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) > ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)

ElseIf (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) < ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

If (Me.TaxClassA = "B") And ((((Me.RRP) / 1.16) * Me.Quantity) > (((Me.UnitPrice) / 1.16) * Me.Quantity)) Then
Me.Taxable = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)
ElseIf (Me.TaxClassA = "B") And ((((Me.RRP) / 1.16) * Me.Quantity) < (((Me.UnitPrice) / 1.16) * Me.Quantity)) Then
Me.Taxable = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

 

[cheekybuddha]

Both of these can be simplified to one-liners (and IMHO less confusing!):

If Me.TaxClassA = "B" Then
  Me.Taxamount = (IIf(Nz(Me.RRP, 0) > Nz(Me.UnitPrice, 0), Nz(Me.RRP, 0), Nz(Me.UnitPrice, 0)) / 1.16) * Nz(Me.Quantity, 0) * 0.16
End If

If Me.TaxClassA = "B" Then
  Me.Taxable = (IIf(Nz(Me.RRP, 0) > Nz(Me.UnitPrice, 0), Nz(Me.RRP, 0), Nz(Me.UnitPrice, 0)) / 1.16) * Nz(Me.Quantity, 0) * 0.16
End If

 

[Josef P.]

If (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) > ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)

ElseIf (Me.TaxClassA = "B") And (((((Me.RRP) / 1.16) * Me.Quantity) * 0.16) < ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)) Then
Me.Taxamount = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

simplify to:

If (Me.TaxClassA = "B") And (Me.RRP > Me.UnitPrice) Then
   Me.Taxamount = ((((Me.RRP) / 1.16) * Me.Quantity) * 0.16)
ElseIf (Me.TaxClassA = "B") And (Me.RRP < Me.UnitPrice) Then
   Me.Taxamount = ((((Me.UnitPrice) / 1.16) * Me.Quantity) * 0.16)
End If

What if RRP and UnitPrice have the same value?

Dim TaxCalcBase As Currency ' or double or Variant or ... depending on what fits
Dim CancelCalc As Boolean

If Me.TaxClassA = "B" Then

   If Me.RRP > Me.UnitPrice Then
      TaxCalcBase = Me.RRP
   ElseIf Me.RRP < Me.UnitPrice Then
      TaxCalcBase = Me.UnitPrice
   Else ' ... Me.RRP = Me.UnitPrice ???
      CancelCalc = True ' ... So that the logic of the code in #7 is not changed.
   End If

   If Not CancelCalc Then ' ... CancelCalc if Me.RRP = Me.UnitPrice
      Me.Taxamount = ((TaxCalcBase / 1.16) * Me.Quantity) * 0.16
      Me.Taxable = Me.Taxamount
   End If

End If

Note: I adopted the logic from the code in #7, even if it seems a bit strange to me that the same condition is used twice and therefore Taxamount is always set to Taxable.