Syntax for Ms Access Array Function

Today tutorial will discuss and explain about Ms Access Array. In programming, Array function is the series of objects which have same size and type. Array function is used in all of the programming language, only differ in syntax functions. In Ms Access, Array function only used in VBA Code. Arrays are declared the same way as other variables, using the Dim, Static, Private, or Public statements.

 

Syntax for Array Function

Array( arglist )
Arglist: the argument list, separated with comma which values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.
 
Example:
Dim Contact, Employee
Contact = Array(“Jane”, “Julie”, “Jenny”, “Jessy”, “Joy”, “Jelly”, “June”)
 
1. Employee = Contact(4)
Employee contains “Jessy”.
 
2. Employee = Contact(2)
Employee contains “Julie”.

 

Array in Tables

Without Array, you may need to set up each variable, such as:

Ms Access Array-1

Ms Access Array-1

Dim BookTitle1 as String, BookTitle2 as String, BookTitleN as String

The table only have 4 records, so it may be simple with non array syntax above. But, what if the table contains 10 or greater records? It may be troublesome and inefficient. This is where you create Array:

Read Also:  MS Access Substring Function

Dim BookTitle(25) as String

The BookTitle served as the Array name, while (25) is the character length of the BookTitle field. String is the Data type.

Array can be used to combine with other codes. Example:

Dim BookID, BookSubject
BookID = Array(“E101”, “E211”, “AB02”)
BookSubject = Right (BookID, 2)
The BookSubject will contains: “E1”, “E2”, “AB”.

 

Array Function in VBA

Ms Access Array-2

Ms Access Array-2

Arrays is like variables. They can be local, module, or global and has any data type, including Variant. The size of the array is limited to an integer (in the range -32,768 to 32,767). The default lower boundary is always 0, but this can be altered by placing an Option Base statement. You can also specify the lower limit for an array by using the “To” keyword.
Example:

Dim BookID, BookSubject
BookID = Array(“E101”, “E211”, “AB02”)
BookSubject = Right (BookID, 2)
Option Base 1
Or
Dim BookID (1 To 20) as VarChar



Access Database Tags: #array of strings vba #array vba excel #arrays in vb #dynamic array in vba #ms access array #ms access vba arrays #vba array function #vba array size #vba redim array

MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in


Must read×

Top