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.
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 Datepart Function and Syntax Query

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.

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

Related Access Database

  • arrat arglist access vba
  • ms access data definition query example
  • access array formula
  • access array function
  • access array functions
  • ms access array function
  • ms access query XOR

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 VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to

Must read×