Thursday, January 30, 2014

Using Arrays in Access

Testing if a value is in an Arrays using Functions

A client of mine recently had a situation where they were granting scholarships based on a particular major.  They needed a program that would "check their work" and either identify students who qualified or verified that recipient's were correct.

Although there were a number of ways to complete this task, I ended up using an Array and figured this would make a good example for the blog.

An Array is used in many programming languages and refers to a set of numbers or objects that will follow a specific pattern.  The set of values for this array was the list of major codes.  There really wasn't anything specific that related these majors as they were picked by a committee based on many different factors.  I could have stored these values in a table, but they will change year to year and are really only needed for this one function.

I'll go through the code here:

1.  First we create a function name ScholarshipMajor.  I will use this function in Queries and pass to it one argument [Major].  We define Major as a string value.
Public Function ScholarshipMajor(Major As String)

2.  Next we define our Array.  I name our Array ValidPlans and define it as Variant.  This way I'm not limited to the number of majors I can list it in.
Dim ValidPlans As Variant

3.  Now we have to load values into our array.  I've shortened the list of majors to make writing this easier.  The original list had 67 majors.  We are going to use the "Split" function to load our values into the Array.  The Split function asks for the string and the delimiter as its 2 arguments. The values are all included between one set of " " and we put the delimiter here in " " as well.  We are using a comma delimiter. 
ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")
At this point, we need to break from this function and write a second one.

4.  Now that we have an Array that lists all valid majors for this scholarship, we need to write a function to test if the passing value (major) is in that array.  The function here can be used to test if any text value is in any array.  You just have to pass the function the array and the value to test for.  The "As Boolean" will default our function to return a -1 or 0 for True or False.

 
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

5.  With this function to test if a value is in an array, we can finish our original function.  Here we will write an IF statement using our newly created IsInArray function.  We will pass that function the major to test for and the array we created.  If IsInArray returns as True (-1) then this function will return True.  Otherwise both functions will return false.

If IsInArray(Major, ValidPlans) Then
ScholarshipMajor = True
Else
ScholarshipMajor = False
End If


This could have been built into one function but I've split out the test part to make it more usable to other functions without re-writing the code over and over.

I've placed all the code in order below.

***************************************************
Public Function ScholarshipMajor(Major As String)
Dim ValidPlans As Variant
ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")

If IsInArray(Major, ValidPlans) Then
    ScholarshipMajor = True
    Else
    ScholarshipMajor = False
End If


End Function

 
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function