Regarding Select Case

[Don]

I asked a question earlier today and was told to use select case to address
my need. I ended up doing so, but there is a small issue I didn't cover. My
original question concerned figuring commissions for sales people. I am
inserting the MVP's suggested code--it worked fine; however, I have a field
in my query called "FULL" that determines whether my sales people earn full
commission or partial commission.

Function fCommission(nCommission As Variant) As Variant
Dim TheCommission As Variant
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 5
Case 20 To 29.999
TheCommission = 7.5
Case Is >= 30
TheCommission = 10
Case Else ' Other values.
TheCommission = 0
End Select
fCommission = TheCommission
End Function

My question is: can I use a select case scenario to determine the commission
rate with a criteria of either full or partial. If I can, then there would
be two commission rates for each case based on the "FULL" field either being
full or partial. If I've been clear as mud, I coud certainly use the help.
If someone could show me how to write it, I'd be forever gratefull.

 

[Jeff Boyce]

Don

Since there are only two choices ("FULL" or not), rather than using a Case
statement, consider using an If...Then statement.

The start of your function could look something like:

Function fCommission(nCommission As Variant) As Variant
Dim TheCommission As Variant
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
If [Full]=True Then
TheCommission = 5
Else
TheCommission = 5 * <<<whatever your 'partial' adjustment
is>>>

....

(Note: you could set the adjustment amount/percentage as a constant in your
Declarations and use the named constant throughout)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

If you are going to use the function approach then you can change the
function to something like the following. Personnally I would use a table
that had
StartVal, EndVal, CommissionRate, and FullPartial and "lookup" the rate.

If the "full" field is not text but is a boolean (yes/no) field then change
the line to
If bFullPartial = True Then

Function fCommission(nCommission As Variant, bFullPartial as Variant) As
Variant
Dim TheCommission As Variant

If bFullPartial = "Full" then
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 5
Case 20 To 29.999
TheCommission = 7.5
Case Is >= 30
TheCommission = 10
Case Else ' Other values.
TheCommission = 0
End Select
ELSE
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 1
Case 20 To 29.999
TheCommission = 2
Case Is >= 30
TheCommission = 3
Case Else ' Other values.
TheCommission = 0
End Select

End If
fCommission = TheCommission

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..