VBA Function Creation

[graviz]

I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:

BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0

Function DishNetActivityCode(service_code_string As String) As Integer

Dim CodeArray, i As Integer

CodeArray = Array("60", "64", "74", "8.", "~R", "R=")
For i = 0 To UBound(CodeArray)
If InStr(service_code_string, CodeArray(i)) Then
DishNetActivityCode = 1
Exit Function
End If
Next
DishNetActivityCode = 0

End Function

Function VideoActivityCode(service_code_string As String) As Integer

Dim CodeArray, i As Integer

CodeArray = Array("6;", "62", "73", "77", "78", "79", "-8", "82", ";$", "<2", "2~")
For i = 0 To UBound(CodeArray)
If InStr(service_code_string, CodeArray(i)) Then
VideoActivityCode = 1
Exit Function
End If
Next
VideoActivityCode = 0

End Function

I hope this explains what I'm trying to do. Any ideas?

 

[rzw0wr]

For simplicity I would leave the as 2 different functions.

Dale

 

[graviz]

For simplicity I would leave the as 2 different functions.

Dale

Sorry I guess I wasn't clear on what I was looking to do. I still want to two seperate functions however I would like to make a 3rd one that yeilds 3 results as shown above.

 

[nanscombe]

You could try concatenating them together like this ...

Public Function ActivityType(service_code_string As String) As String
  ActivityType = DishNetActivityCode(service_code_string)
  ActivityType = ActivityType & VideoActivityCode(service_code_string)

  Select Case ActivityType
  Case "01"
    ActivityType = "V_Only"
  Case "10"
    ActivityType = "BB_Only"
  Case "11"
    ActivityType = "Hybrid"
  Case else
    ActivityType = "Unknown"
  End Select

End Function

 

[ChrisO]

Another approach…

In Access, it is generally not appropriate to store data in code literals; Access has tables for that purpose.

In this case the code literals can have a value from which to index. If the index value is a binary value then the index value can be enumerated. The enumerated value can then be “OR’ed” to determine the action to take.

Two actions require a truth table of four possibilities and each of the four should be accountable.

For example:-

Option Explicit
Option Compare Text


Sub TestIt()
    
    Const DishNet As Integer = 1
    Const Video   As Integer = 2

    Select Case IsCode("78", DishNet) Or IsCode("78", Video)
        Case 0: MsgBox "Neither"
            
        Case 1: MsgBox "DishNet"
            
        Case 2: MsgBox "Video"
            
        Case 3: MsgBox "Both"   [color=green]' Not possible if part of a unique composite key.[/color]
        
    End Select
    
End Sub


Public Function IsCode(ByVal strCode As String, _
                       ByVal intType As Integer) As Integer
                       
    Dim vntReturn As Variant

    vntReturn = DLookup("Type", "tblCodes", _
                        "Code=" & Chr(34) & strCode & Chr(34) & _
                        "And Type=" & intType)
    
    IsCode = IIf(IsNull(vntReturn), 0, vntReturn)

End Function

The same thing can be written in many different ways but the basics still apply.

Access 2003 sample attached.

Chris.

 

[nanscombe]

Unfortunately, the above code would not allow you to search an entered service code for the presence of pairs of characters designating the type of service.

e.g.

yy64xx - Dishnet
yyyyyy62xxxx - Video Activity
yy8.xxx77zzz - Hybrid

However, without knowing what a service code looks like it's difficult to know if the original code, in DishNetActivityCode() or VideoActivityCode(), might give a false positive if a particular pair of characters appears anywhere in the code.

yyyR=78xx - Might be Dishnet Activity where R = 78 or it might be a Hybrid.

 

[ChrisO]

Yep, I was reading the InStr incorrectly.

My last attempt for today:-

Sub TestIt()
    
    Const DishNet As Integer = 1
    Const Video   As Integer = 2

    Select Case IsCode("78", DishNet) + IsCode("78", Video) * Video
        Case 0: MsgBox "V_Only"
            
        Case 1: MsgBox "BB_Only"
            
        Case 2: MsgBox "Not defined"
            
        Case 3: MsgBox "Hybrid"
        
    End Select
    
End Sub


Public Function IsCode(ByVal strCode As String, _
                       ByVal intType As Integer) As Integer
                       
    With CurrentDb.OpenRecordset("Select Code From tblCodes Where Type = " & intType)
        Do Until .EOF
            If InStr(strCode, !Code) Then
                IsCode = 1
                Exit Do
            End If
            .MoveNext
        Loop
    End With
    
End Function

Chris.

 

[nanscombe]

graviz: Just a little discussion going on here, bear with us.

ChrisO:
The original requirement seems to be to use these functions as part of a query on multiple records.

What would be the overhead of reading the table, with the codes in, twice for each record?

From a performance point of view it might be better to use a combination of the two approaches:
1) Read the data from the table into a Global, or Static, array.
2) Use the array to do the search.

However, if the search values are fixed what is the benefit of putting the data into a separate table?

 

[ChrisO]

Too early to say…

>>However, if the search values are fixed what is the benefit of putting the data into a separate table?<<

The real problem is the word if. I don’t know what these codes are but it certainly looks to me as if they may need to change over time.

Performance? I don’t care about performance until something is working correctly. It may prove to be faster in a Global array or TempVars (which I can’t test) or maybe someone will come up with an SQL solution, I don’t know.

But first it has to work correctly and some of the information appears to be incorrect.
For example:-

BB_Only = DishNetActivityCode = 1 And VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 And VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 0

I think:-
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 0
should be:-
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 1


I think the original poster needs to confirm that before we move on.

Chris.

 

[nanscombe]

.. But first it has to work correctly and some of the information appears to be incorrect.
For example:-

BB_Only = DishNetActivityCode = 1 And VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 And VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 0

I think:-
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 0
should be:-
V_Only = DishNetActivityCode = 0 And VideoActivityCode = 1


I think the original poster needs to confirm that before we move on.

Chris.

Indeed. I did rather make that assumption in my code as it didn't seem to be logical otherwise.

 

[Galaxiom]

Gravis.

Notice how Chris has used ByVal in his function argument declarations. This is a good habit to get into. If you don't daclare ByVal the parameter is passed as a reference to the memory location of the variable.

If you change the variable value inside the function then the original value also changes. Even if you don't change the value the declaration confirms that a changed value is not going to be retuned to the parameter.

Sometimes we do return values via the arguments and we will declare ByRef and that declaration alerts the programmer using the function.

 

[Galaxiom]

When setting up functions for diverse returns I often incorporate Optional arguments. Always keep them in mind because they can make a function incredibly versatile.

 

[graviz]

Yep, I was reading the InStr incorrectly.

My last attempt for today:-

Sub TestIt()
 
    Const DishNet As Integer = 1
    Const Video   As Integer = 2
 
    Select Case IsCode("78", DishNet) + IsCode("78", Video) * Video
        Case 0: MsgBox "V_Only"
 
        Case 1: MsgBox "BB_Only"
 
        Case 2: MsgBox "Not defined"
 
        Case 3: MsgBox "Hybrid"
 
    End Select
 
End Sub
 
 
Public Function IsCode(ByVal strCode As String, _
                       ByVal intType As Integer) As Integer
 
    With CurrentDb.OpenRecordset("Select Code From tblCodes Where Type = " & intType)
        Do Until .EOF
            If InStr(strCode, !Code) Then
                IsCode = 1
                Exit Do
            End If
            .MoveNext
        Loop
    End With
 
End Function

Chris.

Chris (and everyone) thanks for all your help on this. I agree putting the codes in a table makes sense. I'm just having a bit of trouble understanding the code as I've never ref a table. What is the "!Code" and " intType"?

 

[nanscombe]

Take a look at the attached database and see what you think.

It has the best of both worlds.

The codes are initially stored in a table then read into arrays.

When either of the functions DishNetActivityCode and VideoActivityCode are used for the first time the data is read from the tables and stored into two arrays dishNetCodesArray and videoCodesArray.

The two functions DishNetActivityCode and VideoActivityCode then work pretty much how you originally designed them to, with just a slight tweak.

 

[ChrisO]

>>What is the "!Code" and " intType"?<<

‘Code’ is the name of the Field in Table tblCodes.
Each Code in the table has a Type flag which has a value of 1 or 2.
‘intType’ is passed to Function IsCode() and it has a value of 1 for DishNet or 2 for Video.
‘intType’ is used to open a recordset of DishNet or Video codes.

We can then run through the recordset and check if ‘Code’ is in the string ‘strCode’.
If it is we then set the IsCode() function to 1 and exit the loop.

Chris.

 

[MarkK]

This is a good candidate for a class. You initialize with the ServiceCodeString and then the object exposes various properties based on that initialization value.

 

[Pat Hartman]

Why use an array or a class when you can use a table? A query that joins to the "lookup" table will be more efficient than either and easier to modify over time. In fact, the users can be given forms to let them modify values in tables but they need a programmer to fix an array or a class.

 

[nanscombe]

There are a couple of things that we don't know:

1) Will this be used as a one off look-up, like on a form, or as part of a query?

If it is a one off then it may not matter if it has to do a partial tablescan to find the result.

If it is to be used as part of a query then it would have to do at least one partial tablescan for each row returned.

I made the, perhaps erroneous, assumption that if it were used in a query then it might be better to read the table of values into arrays first and then use them to perform the lookup. An example is attached to post 14.

2) What does the string that is being searched look like?

I am again making an assumption that it looks like "xxx70yy", "xxxR=yy" or "xx70yyR=zz" where the codes are embedded in the middle of a string which rules out making a straight join to the look-up table.

You would probably need to use a "Like" statement in the Where clause.