GetValuesEx method

Gets values from the current Table object that match the specified criteria. If the query does not match any values, the method returns an empty variant.

Syntax

GetValuesEx([SelectList As String], [Where As String],[GroupBy As String], [Having As String], [OrderBy As String], _
[Parameters], [FieldsInfo]) As Variant

Parameters

Name	Description
SelectList	Optional comma-separated list of column names to retrieve values from the table.
Where	Optional condition for matching column values to retrieve from the table.
GroupBy	Optional column names to group values in the result.
Having	Optional aggregate function that is a condition for grouping returned values.
OrderBy	Optional comma-separated list of column names with which to sort the returned values.
Parameters	Optional value or array of values to be used in a parametric query. The values do not need to be strings. The array should contain a value for each instance of the ? character in the SQL query.
FieldsInfo	Optional variant array to contain information about the returned columns. The information includes: FieldsInfo(0) = Name of the column in the recordset (AS_CI_NAME) FieldsInfo(1) = Data type (AS_CI_SIZE) FieldsInfo(2) = Size (AS_CI_TYPE) FieldsInfo(3) = Column attributes (AS_CI_ATTR) Note    For the values of possible data types, see http://msdn.microsoft.com/en-us/library/ms675318(VS.85).aspx. For the values of possible column attributes, see http://msdn.microsoft.com/en-us/library/ms676553(VS.85).aspx.

Return Value

A 2-dimension variant array with the property values in the second dimension of the array.

Remarks

The GetValuesEx method is functionally equivalent to GetValues. However, the GetValuesEx method supports parameters in the same order as a standard SQL query. This may be more convenient to use than GetValues if you are already familiar with the SQL language.

If the FieldsInfo parameter is specified, that array contains information about the returned value array. This can be useful if you want a generic function that can operate with the results of different queries regardless of the output columns and the order in which order they were requested. Moreover, using FieldsInfo, you can process results from different tables (or queries to external data sources) without specific knowledge of the column names, which may be named differently in different tables but have the same purpose in the context of your processing.

The following example functions demonstrate the use of FieldsInfo.

' Helper function that finds the index of a ShareName column in the results table.
Function FindShareNameIndex(fi)
  FindShareNameIndex = -1
  If IsArray(fi) Then
    For i = 0 To UBound(fi,1)
      If fi(0,i) = "ShareName" And fi(1,i) = 202 Then
        FindShareNameIndex = i
        Exit Function
      End If
    Next
  End If
End Function

Function TestFieldsInfo()
  Dim fi
  Dim res
  res = Vault.Table("GCFShares").GetValues(,,,,,fi)
  If IsArray(fi) Then
    Dim sni
    sni = FindShareNameIndex(fi)
    TestFieldsInfo = ""
    For j = 0 To UBound(res,2)
      TestFieldsInfo = TestFieldsInfo & res(sni,j) & ";"
    Next
  Else
    TestFieldsInfo = fi
  End If
End Function