Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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
  1. The first statement in the above program defines a variable with the name varNumber, as a Variant Type Array for 6 elements.

  2. Another variable j is defined as an integer type that will be used as an index variable in the For. . .Next loop.

  3. 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.

Earlier Post Link References:

Share:

1 comment:

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code