Solved

function using select case not returning results

Posted on 2014-03-07
11
347 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
need to handle null values???
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
Comment Utility
Rey,

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

Author Closing Comment

by:Karen Schaefer
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now