return Count of Select Query

[ilcaa]

I am using VBA and wanted to get the Count of the records and retrieve the first and last record of a SQL Query result.

Select Top 6 qCL from Quotes WHERE qTicker ='GOOG';

it returns the following, which is correct
qCL
556.54
536.19
528.50
534.95
526.75
535.00

First thing I notice I get the wrong count using rs.Fields.Count. It returns 1. It should return 6. Here is part of the VBA code

Dim result as Variant
Dim cn As New ADODB.Connection
cn.Open ConnectionString

Dim query as String
Query = "Select Top 6 qCL from Quotes WHERE qTicker ='GOOG'; "

Dim rs As New ADODB.RecordSet
rs.Open query, cn, adOpenForwardOnly, adLockReadOnly

totalResults = rs.Fields.Count
FirstPrice = rs.Fields(0)
LastPrice = rs.Fields(5)

 

[Joe4]

Your Select query is only returning one field (qCL). So doing a field count from that query should only return one (it is returning the field count from the query, not the underlying table).

If you are looking to return the number of records returned, you want a record count, not a field count.

 

[ilcaa]

so I assume the only thing I need to change is rs.Fields.Count to rs.Records.Count? I did and I get a "Complie Error: Method or data member not found" and the word Records is highlighted

i also tried rs.RecordCount and this actually returns -1

 

[Joe4]

In counting the records in a recordset, you first need to move to the last record.

rs.MoveLast

totalResults = rs.RecordCount

 

[ilcaa]

im getting an error, but I will continue to play with it. Also how do I retrieve the value from the first and last records, it seems rs.records(0) or rs.records(5) does not work if I have 6 records.

 

[Joe4]

Also how do I retrieve the value from the first and last records, it seems rs.records(0) or rs.records(5)

A value would have to come from a field. A record is a collection of fields.

I would recommend doing some research on working with recordsets, specifically returning Record Counts and specific fields. There are lots of tutorials out there, if you do Google Search.

 

[ilcaa]

you mentioned earlier that a field is a column, and the values below are records... this is my results from my Select query, what are each of these prices called? Records? Values? Is the entire set a "RecordSet". I thought it would be easy to grab the results and use it in expressions, like an array, I dont understand why is so complex...

qCL
556.54
536.19
528.50
534.95
526.75
535.00

 

[Joe4]

In comparison to Excel:
Records = Rows
Fields = Columns

In working with Recordsets, if you are trying to return a single value, you need to reference BOTH the Record and Field pieces, even if you result set only has one Record or one Field.

Working with RecordSets can be a little tricky at first, if you are not familiar with the different syntax and how you need to work with them. That is why I recommend checking out some tutorials and educating yourself on them.

Here are a few promising links:
https://blog.udemy.com/vba-recordset/
Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com
https://www.youtube.com/watch?v=rS41hnU8Jbg