Array Data Types
Arrays provide a means of storing a collection of data in a single variable and accessing each element of the array using an index. Unlike .NET arrays which are Base 0, an array in Basic syntax is Base 1. Thus, the first element is referenced using 1 as the index. The maximum size of an array is 1,000 elements.
Basic syntax uses rounded parentheses to specify the array bounds.
X(1) = 5
When you declare an array, specifying the number of elements is optional. If you don’t specify the number of elements in the declaration, before you use the array you have to either re-dimension it using the ReDim statement or assign an existing array to it.
Dim X() As Number ‘Declare a non-dimensioned array
Dim Y(10) As Number 'Declare an array with 10 elements
The ReDim statement is the same as in VB.NET. It will change the number of elements in the array and reset their values. If you want to keep the existing values intact, also use thePreserve keyword.
Redim var(number) ‘Redimension an array and reset all values
Redim Preserve var(number) ‘Redim an array and preserve existing values
Assigning values to an array is done in different ways. If you know what the values of an array are during the development process, you can initialize the array with these values using the Array() function. Pass the Array() function all the elements as a comma delimited list and it returns an array that is fully populated with these elements. When using the Array() function you don’t have to specify the array size. The compiler figures that out for you.
As expected, if you want to simply assign a value to an individual element in the array, the index must be within the array’s bounds. But if you are assigning an entire array to another array variable and they are different sizes, you do not have to re-dimension the target array. The target array will be overwritten and it will have the size of the existing array.
‘Demonstrate initializing an array and then overwriting it
Dim MonthsInSeason() As String
MonthsInSeason = Array("May", "June")
If LongWinter = True Then
MonthsInSeason = Array("June", "July", "August")
End If
If you don’t know the array values during the development process, you will probably assign the initial values to the array by looping through it. A common way of doing this is using a For Next loop where the range of the loop is the lower and upper bounds of the array. Another common method of assigning values to each element is to do so as the report is looping through its detail records. For each pass through the detail section you assign one of the fields to the array.
‘Sample code within the detail section to track customer sales
Dim RecordCounter As Number
Dim SalesDetail() As Number
RecordCounter = RecordCounter + 1
SalesDetail(RecordCounter) = {Customer.Sales}
Once the array is populated, you can test to see if a certain value already exists in the array by using the In operator. Using the In operator saves you the trouble of looping through the entire array searching for a particular value. If the value already exists, the In operator returns True.
‘Sample code to fill an array with the unique zip codes for all customers being printed
Dim RecordCounter As Number
Dim ZipCodes(100) As Number
If Not {Customer.ZipCode} In ZipCodes Then
RecordCounter = RecordCounter + 1
ZipCodes(RecordCounter) = {Customer.ZipCodes}
End If
Basic syntax has many predefined functions for summarizing the values in an array. These functions range from summing the total of all the values in the array to getting the maximum value in the array. Table 8-2 lists the array functions.
Table 8-2. Array Summary Functions
| Basic Function | Description |
|---|---|
| Average(array) | Calculate the average of all numbers. |
| Count(array) | Count how many numbers there are. |
| DistinctCount(array) | Count how many numbers there are without including duplicates. |
| Maximum(array) | Return the maximum number. |
| Minimum(array) | Return the minimum number. |
| PopulationStdDev(array) | Return the Population Standard Deviation calculation. |
| PopulationVariance(array) | Return the PopulationVariance calculation. |
| StdDev(array) | Return the Standard Deviation calculation. |
| Variance(array) | Return the Variance calculation. |
Although the functions are designed to only work with arrays, you can use them with table fields as well. This is done by creating a new array with the Array() function and passing it the fields to work with.
‘Sample code for getting the maximum value of three fields
Dim MaxSales1stQtr As Number
MaxSales1stQtr = Max(Array({Sales.Jan}, {Sales.Feb}, {Sales.Mar}))
Question: I only want to display the record with the most recent date. I s there a formula that does this?
Answer: You can compare the date of each record to the maximum date in the entire report. If they aren’t equal, suppress the record. To implement this, right-click on the Details section and select Section Expert. In the Suppress property, enter the following Basic syntax conditional formula. Remember to replace the dummy field with your report’s date field:
