kidkosmo said:
Any other tricks up your sleeve? I tried using the CDbl() as shown
below, but get the same Type Mismatch error on the variant in the aTAT
= CDbl(vTAT) line. Since I'm plugging data into a temp table, I'm
already removing any null values, so I didn't bother adding the NZ()
function.
BTW...thank you SO much for all of the help you've been providing. If
nothing else, I am learning more than I ever intended.
Dim strSQL As String
Dim vTAT()
Dim aTAT() As Double
Dim lCount As Integer
strSQL = "SELECT TAT from tblTEMP_CR_Calc"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
vTAT = rs.GetRows()
aTAT = CDbl(vTAT)
kidkosmo,
I think you're just stringing me along by pretending not to know
anything, but either way, perhaps this will help someone. From the A97
help file on the GetRows Method:
(begin quote)
Retrieves multiple rows from a Recordset object.
Syntax
Set varArray = recordset.GetRows (numrows)
The GetRows method syntax has the following parts.
Part Description
varArray A Variant that stores the returned data.
recordset An object variable that represents a Recordset object.
numrows A Variant that is equal to the number of rows to retrieve.
Remarks
Use the GetRows method to copy records from a Recordset. GetRows returns
a two-dimensional array. The first subscript identifies the field and
the second identifies the row number. For example, intField represents
the field, and intRecord identifies the row number:
avarRecords(intField, intRecord)
To get the first field value in the second row returned, use code like
the following:
field1 = avarRecords(0,1)
To get the second field value in the first row, use code like the following:
field2 = avarRecords(1,0)
The avarRecords variable automatically becomes a two-dimensional array
when GetRows returns data.
If you request more rows than are available, then GetRows returns only
the number of available rows. You can use the Visual Basic for
Applications UBound function to determine how many rows GetRows actually
retrieved, because the array is sized to fit the number of returned
rows. For example, if you returned the results into a Variant called
varA, you could use the following code to determine how many rows were
actually returned:
numReturned = UBound(varA,2) + 1
You need to use "+ 1" because the first row returned is in the 0 element
of the array. The number of rows that you can retrieve is constrained by
the amount of available memory. You shouldn't use GetRows to retrieve an
entire table into an array if it is large.
Because GetRows returns all fields of the Recordset into the array,
including Memo and Long Binary fields, you might want to use a query
that restricts the fields returned.
After you call GetRows, the current record is positioned at the next
unread row. That is, GetRows has the same effect on the current record
as Move numrows.
If you are trying to retrieve all the rows by using multiple GetRows
calls, use the EOF property to be sure that you're at the end of the
Recordset. GetRows returns less than the number requested if it's at the
end of the Recordset, or if it can't retrieve a row in the range
requested. For example, if you're trying to retrieve 10 records, but you
can't retrieve the fifth record, GetRows returns four records and makes
the fifth record the current record. This will not generate a run-time
error. This might occur if another user deletes a record in a
dynaset-type Recordset. See the example for a demonstration of how to
handle this.
(end quote)
The Example:
(begin quote)
The following example uses the GetRows method to return a
two-dimensional array containing all rows of data in a Recordset object:
Sub RowsArray()
Dim dbs As Database, rst As Recordset, strSQL As String
Dim varRecords As Variant, intI As Integer, intJ As Integer
' Return reference to current database.
Set dbs = CurrentDb
' Build SQL statement that returns specified fields.
strSQL = "SELECT FirstName, LastName, HireDate " _
& "FROM Employees"
' Open dynaset-type Recordset object.
Set rst = dbs.OpenRecordset(strSQL)
' Move to end of recordset.
rst.MoveLast
' Return to first record.
rst.MoveFirst
' Return all rows into array.
varRecords = rst.GetRows(rst.RecordCount)
' Find upper bound of second dimension.
For intI = 0 To UBound(varRecords, 2)
Debug.Print
' Find upper bound of first dimension.
For intJ = 0 To UBound(varRecords, 1)
' Print data from each row in array.
Debug.Print varRecords(intJ, intI)
Next intJ
Next intI
rst.Close
Set dbs = Nothing
End Sub
(end quote)
What it boils down to is that you need to assign the Double values in a
loop while addressing the specific field that you want. An interesting
possibility that the GetRows Method might enable would be for using SQL
to pull a specific data range from an Excel spreadsheet into such a
variant array, excluding certain rows with the WHERE part, provided the
resulting number of rows/records from the spreadsheet are not too large
for the array. 'aTAT = CDbl(vTAT)' or 'aTAT = CDbl(Nz(vTAT))' or even
'vTAT() = aTAT().ConvertToDouble' or 'vTAT() = aTAT().NzConvertToDouble'
would have been a nice feature in Access, but it simply is not there.
So instead of being able to do the most intuitive thing (as was your
guess), we are forced to loop through the elements. It's not a major
issue, though, since it can be done in a few lines of code. Post back
if you need help in creating that loop.
James A. Fortune
(e-mail address removed)
