Simplify If Statement and Loop Code (1 Viewer)

[graviz]

Is there any way to simplify and shorten my code. It works but I feel like I could shorten it somehow.

Function DishNet_Converted_Type(service_code_string As String) As String
Dim CodeArray, i As Integer
NC_CodeArray = Array("60")
CH_CodeArray = Array("64", "74", "8.", "R=")
TC_CodeArray = Array("~R")

For i = 0 To UBound(NC_CodeArray)
If InStr(service_code_string, NC_CodeArray(i)) Then
DishNet_Converted_Type = "NC"
Exit Function
End If

Next
For i = 0 To UBound(CH_CodeArray)
If InStr(service_code_string, CH_CodeArray(i)) Then
DishNet_Converted_Type = "CH"
Exit Function
End If
Next
For i = 0 To UBound(TC_CodeArray)
If InStr(service_code_string, TC_CodeArray(i)) Then
DishNet_Converted_Type = "TC"
Exit Function
End If
Next
DishNet_Converted_Type = "Error"
End Function

Any ideas?

 

[MarkK]

You could do this . . .

Function Converted_Type(service_code As String) As String
    Select Case service_code
        Case "60"
            Converted_Type = "NC"
        Case "64", "74", "8.", "R="
            Converted_Type = "CH"
        Case "~R"
            Converted_Type = "TC"
        Case Else
            Converted_Type "Error"
    End Select
End Function

. . . but I would never hard-code data like that. That would be far more flexible in a table . . .

tblDishnetConvert
DishnetConvertID
ConvertedCode
ServiceCode

. . . and data like . .

1, "NC", "60"
2, "CH", "64"
3, "CH", "74"
4, "CH", "8."
5, "CH", "R="
6, "TC", "~R"

. . . and then your dishnet conversion function can look like . . .

Function Converted_Type(service_code As String) As String
   ConvertedType = DLookup("ConvertedCode", "tblDishnetConvert", "ServiceCode = '" & service_code & "'")
End Function

. . . and then you can add conversion parameters down the road without re-writing your code.

 

[graviz]

You could do this . . .

Function Converted_Type(service_code As String) As String
    Select Case service_code
        Case "60"
            Converted_Type = "NC"
        Case "64", "74", "8.", "R="
            Converted_Type = "CH"
        Case "~R"
            Converted_Type = "TC"
        Case Else
            Converted_Type "Error"
    End Select
End Function

. . . but I would never hard-code data like that. That would be far more flexible in a table . . .

. . . and data like . .

1, "NC", "60"
2, "CH", "64"
3, "CH", "74"
4, "CH", "8."
5, "CH", "R="
6, "TC", "~R"

. . . and then your dishnet conversion function can look like . . .

Function Converted_Type(service_code As String) As String
   ConvertedType = DLookup("ConvertedCode", "tblDishnetConvert", "ServiceCode = '" & service_code & "'")
End Function

. . . and then you can add conversion parameters down the road without re-writing your code.

The only thing is I need it to search through a field. The field it is testing against is setup like so: "rr;qe;60;2p" and so on. Would you code work for that?

 

[MarkK]

That's not a field, that is many fields. Check out the Split() function to tease those apart.

 

[graviz]

That's not a field, that is many fields. Check out the Split() function to tease those apart.

I'm only testing one field in my queries "service_code_string"

In your case statement it appears it would only yield one of those results if the field was equal to for example "60". The problem is that field would have a lot more codes in addition to the one I am looking for.

Here's what's in the field for one of the records.

Y:|OR|=(|0Q|ZT|$=|AC|PZ|AA|60|CW|IK|}{|ZH|!.|1{|1A|1D|**

Does that make more sense?

 

[pr2-eugin]

I have used Mark's initial Code and suggestion of using Split function and have derived the following, see if it helps..

Function getDishNetType(codeStr As String) As String

[COLOR=SeaGreen]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]

    Dim CodeArray, i As Integer
    Dim tmpArr() As String, tmpStr As String
    tmpArr = [URL="http://msdn.microsoft.com/en-gb/library/6x627e5f%28v=vs.80%29.aspx"]Split[/URL](codeStr, "|")
    tmpStr = "Error"
    
    For i = 0 To UBound(tmpArr)
        Select Case tmpArr(i)
            Case "60"
                tmpStr = "NC"
                Exit For
            Case "64", "74", "8.", "R="
                tmpStr = "CH"
                Exit For
            Case "~R"
                tmpStr = "TC"
                Exit For
        End Select
    Next
    
    getDishNetType = tmpStr
End Function

But again I would strongly advice as Mark has suggested the use of Table over hard coding..

EDIT: Messed up return statement.. Now fixed..