Solved

function using select case not returning results

Posted on 2014-03-07
11
349 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
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 250 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 250 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

18 Experts available now in Live!

Get 1:1 Help Now