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:
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:
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
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