Solved

function using select case not returning results

Posted on 2014-03-07
11
352 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find unused columns in a table 12 77
Options for Linking SQL tables to Access 2013 9 44
Sort order not as I expected 13 21
Balance After Payment 12 21
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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