Case Statement Problem

[Joe]

Hello,

Can someone please help me with my Case Statement

Public Function FEE_TYPE(DET_REF As String) As String
Dim strRef As String
Dim strFeeType As String

strRef = DET_REF

Select Case strRef
Case "LP*"
strFeeType = "LP"
Case "PP*"
strFeeType = "PPP"
Case "Prop*"
strFeeType = "Prop Tax"
End Select
FEE_TYPE = strFeeType
End Function

Query
SELECT FEE_CATEGORY: FEE_TYPE(DET_REF)
FROM tblFees

I do not get any results when I know there are many of them

 

[Arvin Meyer [MVP]]

Try this:

Public Function FEE_TYPE(strRef As String) As String
Dim strFeeType As String

Select Case strRef
Case "LP*"
strFeeType = "LP"
Case "PP*"
strFeeType = "PPP"
Case "Prop*"
strFeeType = "Prop Tax"
End Select
FEE_TYPE = strFeeType
End Function

Query
SELECT FEE_CATEGORY, FEE_TYPE([DET_REF]) As FeeType
FROM tblFees

 

[Douglas J. Steele]

What are your values for DET_REF? If you're trying to use the asterisk as a
wild card so that, for example, you're hoping to change any values starting
LP to LP, it won't work.

You'd need to use

Public Function FEE_TYPE(DET_REF As String) As String
Dim strRef As String
Dim strFeeType As String

strRef = DET_REF

If Left$(strRef, 2) = "LP" Then
strFeeType = "LP"
ElseIf Left$(strRef, 2) = "PP" Then
strFeeType = "PPP"
ElseIf Left$(strRef, 4) = "Prop" Then
strFeeType = "Prop Tax"
End If
FEE_TYPE = strFeeType
End Function

 

[Joe]

Perfect!!! Thank you

Try this:

Public Function FEE_TYPE(strRef As String) As String
Dim strFeeType As String

Select Case strRef
Case "LP*"
strFeeType = "LP"
Case "PP*"
strFeeType = "PPP"
Case "Prop*"
strFeeType = "Prop Tax"
End Select
FEE_TYPE = strFeeType
End Function

Query
SELECT FEE_CATEGORY, FEE_TYPE([DET_REF]) As FeeType
FROM tblFees
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Hello,

Can someone please help me with my Case Statement

Public Function FEE_TYPE(DET_REF As String) As String
Dim strRef As String
Dim strFeeType As String

strRef = DET_REF

Select Case strRef
Case "LP*"
strFeeType = "LP"
Case "PP*"
strFeeType = "PPP"
Case "Prop*"
strFeeType = "Prop Tax"
End Select
FEE_TYPE = strFeeType
End Function

Query
SELECT FEE_CATEGORY: FEE_TYPE(DET_REF)
FROM tblFees

I do not get any results when I know there are many of them

.