Solved: Select Case not working

[jmenche]

Howdy,

I wrote this function and stored it in a module. However, it comes up blank when I call it in a select query and I am sure that the source field that I am using is in Currency format. Anyone have any ideas?

Thanks

Public Function ABCStore(Sales As Currency) As String
'Segment stores based on dollar volume
Select Case Sales
Case Sales < 1000: ABCStore = "H"
Case Sales < 5000 And Sales >= 1000: ABCStore = "G"
Case Sales < 10000 And Sales >= 5000: ABCStore = "F"
Case Sales < 15000 And Sales >= 10000: ABCStore = "E"
Case Sales < 25000 And Sales >= 15000: ABCStore = "D"
Case Sales < 50000 And Sales >= 25000: ABCStore = "C"
Case Sales < 100000 And Sales >= 50000: ABCStore = "B"
Case Sales >= 100000: ABCStore = "A"
End Select
End Function

[Norie]

Have you tried setting a breakpoint in the code and stepping through it?

[xCav8r]

jmenche, http://vbaexpress.com/forum/images/smilies/039.gif

Your problem is that you wrote it thinking like If..Then...Else. Swap Is for Sales, and it will work as you want it to work. Or, better yet, stick with If. ;)

Public Function ABCStore(Sales As Currency) As String
'Segment stores based on dollar volume

' If Sales < 1000 Then
' ABCStore = "H"
' ElseIf Sales < 5000 And Sales >= 1000 Then
' ABCStore = "G"
' ElseIf Sales < 10000 And Sales >= 5000 Then
' ABCStore = "F"
' ElseIf Sales < 15000 And Sales >= 10000 Then
' ABCStore = "E"
' ElseIf Sales < 25000 And Sales >= 15000 Then
' ABCStore = "D"
' ElseIf Sales < 50000 And Sales >= 25000 Then
' ABCStore = "C"
' ElseIf Sales < 100000 And Sales >= 50000 Then
' ABCStore = "B"
' ElseIf Sales >= 100000 Then
' ABCStore = "A"
' Else
' End If

Select Case Sales
Case Is < 1000
ABCStore = "H"
Case Is < 5000 And Sales >= 1000
ABCStore = "G"
Case Is < 10000 And Sales >= 5000
ABCStore = "F"
Case Is < 15000 And Sales >= 10000
ABCStore = "E"
Case Is < 25000 And Sales >= 15000
ABCStore = "D"
Case Is < 50000 And Sales >= 25000
ABCStore = "C"
Case Is < 100000 And Sales >= 50000
ABCStore = "B"
Case Is >= 100000
ABCStore = "A"
Case Else
End Select
End Function

Sub RunTests()
TestABCStore 999
TestABCStore 1000
TestABCStore 4999
TestABCStore 5000
TestABCStore 9999
TestABCStore 10000
TestABCStore 14999
TestABCStore 15000
TestABCStore 49999
TestABCStore 50000
TestABCStore 99999
TestABCStore 100000
End Sub

Sub TestABCStore(TestValue As Currency)
Debug.Print "$" & TestValue & "=" & ABCStore(TestValue)
End Sub


Results:

$999=H
$1000=G
$4999=G
$5000=F
$9999=F
$10000=E
$14999=E
$15000=D
$49999=C
$50000=B
$99999=B
$100000=A

[geekgirlau]

Just thought I'd throw another variation in the mix!


Public Function ABCStore(Sales As Currency) As String
Select Case Sales
Case Is < 1000: ABCStore = "H"
Case 1000 To 4999: ABCStore = "G"
Case 5000 To 9999: ABCStore = "F"
Case 10000 To 14999: ABCStore = "E"
Case 15000 To 24999: ABCStore = "D"
Case 25000 To 49999: ABCStore = "C"
Case 50000 To 99999: ABCStore = "B"
Case Is >= 100000: ABCStore = "A"
End Select
End Function



By the way jmenche, I've edited your post to put VBA tags around your code - just makes it easier to read. You can do this by selecting the text and clicking on the "VBA" button.

[mdmackillop]

As the Select will exit at the first found value, why not
Public Function ABCStore(Sales As Currency) As String
Select Case Sales
Case Is < 1000: ABCStore = "H"
Case Is < 5000: ABCStore = "G"
Case Is < 10000: ABCStore = "F"
Case Is < 15000: ABCStore = "E"
Case Is < 25000: ABCStore = "D"
Case Is < 50000: ABCStore = "C"
Case Is < 100000: ABCStore = "B"
Case Else: ABCStore = "A"
End Select
End Function

[xCav8r]

Good variations, geekgirl and malcom. Good for conversation. :)

jmenche,

Malcom's suggestion is what I would recommend that you use. It accomplishes what you want (assigns a letter for every possibility), requires less comparisons, and is easier to read.

Geekgirl's suggestion would be the best route if you had different values for your letters. Say, for example, that H were between 500 and 999, G were between 4000 and 4999, F were between 8000 and 8999, etc. In other words, you didn't have a letter for every possibility. In that case, her suggestion would be the most succint way to write the conditional.

If, on the other hand, you were comparing two different things like Sales and Costs, then the best route would be to use If...Then...Else.

HTH!

PS. I edited the thread title to change "function" to "select case" to make it more descriptive.

[jmenche]

It works fine now.

:-)