Join Split Array Functions ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, November 12, 2010

Join Split Array Functions

Introduction

The Joint/Split MS-Access functions are not so popular or used frequently in programs, but their usage is very interesting and powerful too. Let us take them one by one and learn how powerful they are. We will write a program later to demonstrate their usage in real-world programs.

Let us take the Array() Function first. Before we try the Array() Function let us find out how to define an array variable and assign values to each element of the Array.

Demo of Array() Function

'Dimension the array for six elements
Dim varNumber(0 To 5) As Variant, j As Integer
For j = 0 To 5
    varNumber(j) = j + 1
Next

The first statement in the above program defines a variable with the name varNumber, as a Variant Type Array for 6 elements.
Another variable j is defined as an integer type that will be used as an index variable in the For. . .Next loop.
The next three statements in the above program assign values 1 to 6 to the Array elements as:

varNumber(0) = 1
varNumber(1) = 2
varNumber(2) = 3
varNumber(3) = 4
varNumber(4) = 5
varNumber(5) = 6

If we attempt to assign a value to element varNumber(6) then it will run into an error because we have not dimensioned the Array beyond the varNumber(5) element.

We can do this task with only one statement if we use the Array() Function as below:

varNumber = Array(1,2,3,4,5,6)

We don't have to define the variable for a fixed number of elements as we did in the first statement, it does this task automatically depending on the number of items in the argument list. We have used constant values 1 to 6 to assign to the array elements. Another thing to keep in mind is that the target variable varNumber must be always defined as a Variant Type Variable. That gives us more flexibility in assigning mixed Data Type values into different elements of the target variable like the example given below:

varNumber = Array("Nancy",25,"Andrew",30,172.5)

We have assigned a mix of String, Integer, and Double Data Type values into different elements of the same array. Again we have used constant values to assign to the array.

This Function is very useful to pass several parameters to a Program as a single block without defining several parameter declarations in the main program.

You can use Constants, Variables, or data Field Values to assign values to the array.

Example-1:

a = "Nancy"
 b = 25
 c = 172.5
 varNumber = Arrary(a,b,c)

Example-2:

varNumber = Array(Me![FirstName],Me![BirthDate],Me![Address])

Next, we will examine the usage of the Join() Function. Let us make another array of values for this function so that its usage is understood easily.

varNumber = Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")

We have the names of weekdays in the array variable varNumber.

varNumber(0) = "Sun"
 .
 . 
 . 
 varNumber(6) = "Sat"

If you want to combine all values from seven elements of this array variable and create a single string; each item separated with commas (like Sun, Mon, Tue, Wed, Thu, Fri, Sat) then you must write the following statements to achieve this result:

Dim strWeeks as string, j as integer
strWeeks=""
For j = 0 to 6
   if j=6 then
     strWeeks = strWeeks & varNumber(j)
   Else
     strWeeks = strWeeks & varNumber(j) & ","
   end if
Next

Join() Function:

The above task takes only one statement with the Join() Function:

strWeeks = Join(varNumber,",")

The first parameter to the Join() Function is the array of values to be joined together to form a string. The second parameter is the item separator character; comma, if omitted a space character will be used as a separator character by default, otherwise whatever character you specify will be used as the separator.

Result: strWeeks = "Sun,Mon,Tue,Wed,Thu,Fri,Sat"

Split() Function:

Split() is the complementary Function of Join(). It splits the individual item separated by the delimiter character and stores the values into an array variable of Variant Type.

We need the following lines of code to do the same task of Split() Function:

Dim strWeeks(0 To 6) As Variant, strtxt As String
Dim j As Integer, k As Integer

strtxt = "Sun,Mon,Tue,Wed,Thu,Fri,Sat"
k = 0
For j = 1 To Len(strtxt) Step 4
   strWeeks(k) = Mid(strtxt, j, 3)
   k = k + 1
Next

With the use of the Split() Function it takes only one statement to do the job that we did with the above program:

strWeeks = Split(strTxt,",")

Next week we will use these functions in a Program to redefine a Query linked to a Form to filter and view data.