Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

function using select case not returning results

Posted on 2014-03-07
11
351 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WordMerge Module will not automatically open and close Word and excel 2013 4 32
table joins in qry 17 61
Sum in Split Form 17 27
Problem with vba code 4 39
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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