Improve company productivity with a Business Account.Sign Up

x
?
Solved

function using select case not returning results

Posted on 2014-03-07
11
Medium Priority
?
359 Views
Last Modified: 2014-03-07
I need to set the value of a field in a query from a number value to the text value.

Query fails to return values, but query works when I remove the function - what am I missing?


Function MgmtFee(ByVal n As String) As String
Select Case MgmtFee
    Case 1
        n = "Annual"
    Case 2
        n = "Qtrly"
    Case 3
        n = "None"
      MgmtFee = n
    End Select
End Function

Open in new window

0
Comment
Question by:Karen Schaefer
  • 6
  • 3
  • 2
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39913219
Function MgmtFee(ByVal n As String) As String
Select Case MgmtFee
    Case 1
        n = "Annual"
    Case 2
        n = "Qtrly"
    Case 3
        n = "None"
 ' MOVE THIS OUT OF SELECT     MgmtFee = n
    End Select

      MgmtFee = n

End Function
0
 
LVL 11

Expert Comment

by:MajorBigDeal
ID: 39913225
I think it is because when calling outside the function you have a variable called MgmtFee already set to a value. But when calling the function MgmtFee does not yet have a value inside the function.
0
 
LVL 11

Assisted Solution

by:MajorBigDeal
MajorBigDeal earned 1000 total points
ID: 39913242
I would change the case variable to n,  for example:

Function MgmtFee(ByVal n As String) As String
Select Case n
    Case 1
        MgmtFee = "Annual"
    Case 2
        MgmtFee = "Qtrly"
    Case 3
        MgmtFee = "None"
    End Select
End Function
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

Author Comment

by:Karen Schaefer
ID: 39913254
ok Now getting typemismatch

the field type is a number as a text.

I tried variant, string, long,   they all seem to be returning a value of 3 when the actual value is a number 1.

Function MgmtFee(ByVal n As String) As String
Select Case MgmtFee 'Mgmt_Fee: MgmtFee([MgtFeeTiming])
    Case 1
        n = "Annual"
    Case 2
        n = "Qtrly"
    Case 3
        n = "None"
    End Select
      MgmtFee = n
End Function
0
 

Author Comment

by:Karen Schaefer
ID: 39913260
I did as you suggest, however, still getting typemismatch

Function MgmtFee(ByVal n As String) As String
Select Case n 'Mgmt_Fee: MgmtFee([MgtFeeTiming])
    Case 1
        MgmtFee = "Annual"
    Case 2
        MgmtFee = "Qtrly"
    Case 3
        MgmtFee = "None"
    End Select
End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39913274
change this

Function MgmtFee(ByVal n As String) As String

to

Function MgmtFee(ByVal v As Integer) As String

or

Function MgmtFee(ByVal v) As String




Function MgmtFee(ByVal v As Integer) As String
dim n as string
Select Case v
    Case 1
        n = "Annual"
    Case 2
        n = "Qtrly"
    Case 3
        n = "None"

    End Select

      MgmtFee = n

End Function
0
 

Author Comment

by:Karen Schaefer
ID: 39913283
need to handle null values???
0
 

Author Comment

by:Karen Schaefer
ID: 39913294
still getting Datatype mismatch in criteria expression.

Function MgmtFee(ByVal n As Integer) As String
Select Case Nz(n, 0) 'Mgmt_Fee: MgmtFee([MgtFeeTiming])
    Case 1
        MgmtFee = "Annual"
    Case 2
        MgmtFee = "Qtrly"
    Case 3
        MgmtFee = "None"
    Case 0
        MgmtFee = ""
    End Select
End Function

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39913307
my last post


Function MgmtFee(ByVal n) As String
if n & ""<>"" then  MgmtFee="" : Exit Function
Select Case n
    Case 1
        MgmtFee = "Annual"
    Case 2
        MgmtFee = "Qtrly"
    Case 3
        MgmtFee = "None"
    Case 0
        MgmtFee = ""
    End Select
End Function
0
 

Author Comment

by:Karen Schaefer
ID: 39913335
Rey,

it completes the code, however,it does not return any values -
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39913351
Here is the final results - thanks for the input.

Function MgmtFee(ByVal n) As String 'Mgmt_Fee: MgmtFee([MgtFeeTiming])
Select Case n
    Case 1
        MgmtFee = "Annual"
    Case 2
        MgmtFee = "Qtrly"
    Case 3
        MgmtFee = "None"
    Case 0
        MgmtFee = ""
    End Select
End Function
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

602 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question