Percentage question

[NascarBaritone]

I am totally stumped on this one, so it probably means it's an easy answer.

I have two textbox on my report that receive data from an Excel spreadsheet that is imported into my table:

Principal Amount
Commission Amount

I want to fill another textbox with a "Red Flag" if the Commission Amount is 3% or greater than the Principal Amount. I have successfully done that with this code:

If Me.Commission_Amount.Value >= Me.Principal_Amount.Value * 0.03 Then
Me.txtRedFlag8.Value = "- The Commission of the trade was 3% or greater."
Else
Me.txtRedFlag8.Value = ""
End If

However, if the product was sold (not bought) the Principal Amount is a negative value and the code views any commission as being greater than 3%.

For example:

[Principal Amount] of -17932.76 * 0.03 = -537.98
[Commission Amount] of 257.58

The above code views this as greater than 3%, but it obviously is not.

Suggestions?

 

[ChipperT]

I'd do this:

If (Me.Commission_Amount.Value >= Me.Principal_Amount.Value * 0.03) AND (Me.Principal_Amount.Value > 0) Then
Me.txtRedFlag8.Value = "- The Commission of the trade was 3% or greater."
Else
Me.txtRedFlag8.Value = ""

End If

 

[NascarBaritone]

I've decided this forum has inspirational powers. As soon as I hit the post button the very same sort of idea came to me.

Thanks for the suggestion, ChipperT. The only problem with that one was that it didn't catch those sells that are above the 3% threshold.

I did tweak it to come up with a solution, however.

If (Me.Commission_Amount.Value >= Me.Principal_Amount.Value * 0.03) And (Me.Principal_Amount.Value > 0) Then
Me.txtRedFlag8.Value = "- The Commission of the trade was 3% or greater."
ElseIf (Me.Commission_Amount.Value >= Me.Principal_Amount.Value * -0.03) And (Me.Principal_Amount.Value < 0) Then
Me.txtRedFlag8.Value = "- The Commission of the trade was 3% or greater."
Else
Me.txtRedFlag8.Value = ""
End If

 

[boblarson]

A simpler method would just be to compare the Absolute values using the Abs function.

 

[NascarBaritone]

Thanks, Bob.

I completely forgot about the Abs function. I have used it many times in Excel, but didn't even think about it for Access. Duh!

If Me.Commission_Amount.Value >= Abs(Me.Principal_Amount.Value) * 0.03 Then
Me.txtRedFlag8.Value = "- The Commission of the trade was 3% or greater."
Else
Me.txtRedFlag8.Value = ""
End If