From: Joe on
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 :(
From: Arvin Meyer [MVP] on
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


"Joe" <Joe(a)discussions.microsoft.com> wrote in message
news:58DF4C03-C78E-45CE-BBF4-9A7161E908FA(a)microsoft.com...
> 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 :(


From: Douglas J. Steele on
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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Joe" <Joe(a)discussions.microsoft.com> wrote in message
news:58DF4C03-C78E-45CE-BBF4-9A7161E908FA(a)microsoft.com...
> 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 :(


From: Joe on
Perfect!!! Thank you

"Arvin Meyer [MVP]" wrote:

> 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
>
>
> "Joe" <Joe(a)discussions.microsoft.com> wrote in message
> news:58DF4C03-C78E-45CE-BBF4-9A7161E908FA(a)microsoft.com...
> > 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 :(
>
>
> .
>