DCount Recordset? 2

[cranebill]

Can you DCount a recordset? I can Dcount a table or query but when I DCount a Recordset it says it cannot find it. If I cannot use the dcount function is there a way that I can have something similiar to use with a recordset. I am using access 2002 and this is an ADO recordset.

Bill

 

[rdodge]

Use the RecordCount Property on the ADO Recordset. Example:

NumberOfRecords = arsMyADORecordset.RecordCount

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[cranebill]

I thought about recordcount but was looking at something more like Dcount because you can specify a criteria.

Dcount ("Status","RS1","Status = 1&quot
Dcount ("Status","RS1","Status = 2&quot
Dcount ("Status","RS1","Status = 3&quot
etc

I guess I could just write another recordset for each one then do a recordcount on the result but was thinking there may be an easier way...

Any Ideas welcome

Bill

 

[jebry]

Hi Bill!

I think you need to either open a new recordset or loop through the existing one. It would probably be best to write your own DCount function that does the work based on the field, SQL and criteria passed to it and returns the record count.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[cranebill]

When you say loop through it you mean something like:

RS1.MoveFirst
Do Until RS1.EOF
If RS1.Status = 1 Then Counter = Counter + 1
RS1.MoveNext
Loop

 

[rdodge]

What I have done for that, right the SQL Statement within the OpenRecordset Method, which allows for the criteria aspect that you are looking for, then use the recordcount. Of course, if you prefer, you can always use the FindNext Method looping through the recordset and checking the NoMatch Property.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jebry]

Hi Bill!

That is exactly the sort of looping I was talking about. You can actually do all three counts at once using a Select Case statement to increment the appropriate counter.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[cranebill]

Thanx for the ideas Guys

Bill

 

[rdodge]

I just realized something, to make your life easier and without having to loop through the recordset, why not set the SQL Statement to Group By Status, and in the Select Clause, use the Aggregate Count function.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[cranebill]

Ummm never had any experience with that... can you elaborate?

 

[rdodge]

Example:

SELECT Table.Status, Count(Table.Item) AS CountOfItem
FROM Table
GROUP BY Table.Status

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[cranebill]

Ok look at this and tell me what happened lol...

MsgBox Rs1.RecordCount
Rs1.MoveFirst
MsgBox "Quote Type = " & Rs1!QuoteTypeID & Chr(10) & Chr(13) & "Open = " & QOpen & Chr(10) & Chr(13) & "Lost = " & QLost & Chr(10) & Chr(13) & "Won = " & QWon & Chr(10) & Chr(13) & "Expired = " & QExpired & Chr(10) & Chr(13) & "Closed = " & QClosed

Do Until Rs1.EOF
Select Case Rs1!StatusID
Case 1
QOpen = QOpen + 1
Case 2
QLost = QLost + 1
Case 3
QWon = QWon + 3
Case 4
QExpired = QExpired + 1
Case 5
QClosed = QClosed + 1
End Select
Rs1.MoveNext
Loop
MsgBox "Open = " & QOpen & Chr(10) & Chr(13) & "Lost = " & QLost & Chr(10) & Chr(13) & "Won = " & QWon & Chr(10) & Chr(13) & "Expired = " & QExpired & Chr(10) & Chr(13) & "Closed = " & QClosed

Messagebox number 1 was to verify for me how many records total which was 43

Messagebox Number 2 Was to make sure all variables were set to 0 and to show which Quote Type I was querying

Messagebox Number 3 was to show me the count which all added together came to 51.

How can messagebox number 3 be 51 if there were only 43 records according to a recordcount?

Bill

 

[cranebill]

Nevermind lol... stupid typo...

QWon = QWon + 3

Should be

QWon = QWon + 1

I am just glad the weekend is here in half a day lol

Bill

 

[rdodge]

For where you have:

Chr(10) & Chr(13)

I would replace that with:

vbCrLf

as the vbCrLf is the constant and much easier to work with. Yeah, typos is something we all have to watch out for.

What I was thinking of is the following:

Rs1.Open "SELECT Table.Status, Count(Table.Item) AS CountOfItem " & _
"FROM Table " & _
"GROUP BY Table.Status;",Connection,adOpenDynamic,adLockReadOnly

This shouold produce a recordset that has only as many records as there are how many different status codes, and each record would contain a status code with the number of records in the table that has that status code.

Rs1.MoveFirst

Do While Not Rs1.EOF
Message = Message & vbCrLf & Rs1.Status & ": " & Rs1.CountOfItem
Loop

MsgBox Message, 48

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[cranebill]

I see where this is going and would work well... in this instance however the sql is dynamic and may be more trouble integrating it with the sql that i already have working lol

It is friday and if it were Monday I may attempt it lol. I will use your example in the future though

Bill

 

[rdodge]

I just used the adOpenDynamic as an example. It could be any of the other 4 instead:

adOpenFowardOnly
adOpenKeyset
adOpenStatic
adOpenUnspecified

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[cranebill]

I already used the example you have given me with the "count" Thanx again.

Bill