[RESOLVED] Access Group By

[MartinLiss]

I have a table called Requests and I'd like to return values grouped by a field named 'Sentdate'. I tried the following but got a recordcount of -1. What am I doing wrong?

rs.Open "SELECT count(*) FROM Requests group by SentDate", cn, adOpenDynamic, adLockOptimistic

[techgnome]

1) your field needs an alias...
2) since your field isn't aliased, I assume that by "recordcount" you mean the recordset.RecordCount ... a value of -1 is actually good... that means there is at least one record... but it hasn't been returned yet... you're using a Dynamic recordset... means it'll grab records on demand as you move to the next record...
3) you may want to also return SentDate... otherwise you're just going to get a string of numbers, with no context.

-tg

[MartinLiss]

Okay I did this. How do I display the count?


.Open "SELECT count(*), SentDate FROM Requests group by SentDate", cn, adOpenDynamic, adLockOptimistic
.MoveFirst
Do While Not .EOF
MsgBox rs.Fields("SentDate")
MsgBox rs.Count '???
Loop

[MartinLiss]

Got it

MsgBox rs.Fields(0)

Thanks.