Case Statement Problem
in [General]
|
Prev: Multivalue Field in Access 2007...
Next: Windows 7 will not take Corel Print House software it's old
From: Joe on 14 May 2010 18:45 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 14 May 2010 19:13 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 14 May 2010 19:51 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 14 May 2010 20:05
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 :( > > > . > |