If statement in VBA code - please help

[rca]

Hello Everyone,

I have the following code:

___________________________________________
Function getFee(Symbol, Location, TransactionDate, Quantity, TransactionType)

If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
End If


'Evaluate the 1st symbol by Location and TransactionDate
If Symbol = "A" Or Symbol = "B" Then

Select Case Location
Case "New York"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("09/30/05")
getFee = Abs(Quantity) * 0.3
Case DateValue("10/01/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.45
Case Else
getFee = "???"
End Select

Case Location = "Los Angeles"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.3
Case Else
getFee = "???"
End Select

End Select
End If

'Evaluate the 2nd symbol by Location and TransactionDate
If Symbol = "C" Then

Select Case Location
Case "New York"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.9
Case Else
getFee = "???"
End Select

Case Location = "Los Angeles"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.85
Case Else
getFee = "???"
End Select

End Select
End If

End Function
________________________________________

The code above returns a fee based on the location and date of the transaction. Here's the problem:

The code is suppose to return ZERO as the fee when the transaction is a "Close Out". (I tell the code to multiply by 0, thereby rendering the result 0.)

I thought that this snippet of code would do that:
________________________________________
If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
End If
________________________________________

But, the code seems to 'skip' over this section of the code and returns a value regardless of whether the TransactionType is a 'Close Out' of not.

Can someone see what is wrong with the section of the code that is suppose to return the Fee as ZERO if the TransactionType is a 'Close Out'?

Thank you,
Rachel

 

[Oorang]

Might be an issue where your users are putting in leading or trailing spaces and/or weird capitalization. Try this:

If Trim(StrConv(TransactionType, 2)) = "close out" Then
    getFee = Abs(Quantity) * 0
    End If

 

[rca]

Hi Oorang,

I tried your suggestion and that code still returns a non-zero value. I'm not sure why? It should definitely return a zero value and disregard the rest of the code. I'm not sure why it doesn't. Any other ideas?

Thanks,
Rach

 

[Sharkie21]

Hrmm you can probably look at the values in debug mode to see what's the difference.

You might have a better chance with, but not sure was to lazy to see what the 2 means for strConv.

If Trim(LCase(TransactionType)) = "close out" Then

 

[rca]

Hi Sharkie,

Well, I tried your suggestion as well. The code still returns a non-zero value. It seems like it's just 'skipping' that piece of the code that's suppose to trap the 'Close Out' transaction types. It's sooo strange.

What am I doing wrong?

Thanks,
Rachel

 

[Seti]

Maybe this?

If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
Exit Function
End If

 

[rca]

Thank you Seti! That works. What a silly mistake I made by not putting Exit Function!

I would like to thank all of you for your expert help!

Have a great day!
Rachel