I have a lotto program where I would like to count how many time a number has been entered in each Field.
db = Lotto
table = Cash3
fields = Zero, One, Two ect.
Code give correct count for field Zero, but all the rest are wrong.

Dim cn As adodb.Connection Dim rs As adodb.Recordset     Call SetWindowPos(Me.hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)     frmCash3.Left = (Screen.Width - frmCash3.Width) / 2     frmCash3.Top = (Screen.Height - frmCash3.Height) / 2     Me.MousePointer = vbHourglass     Set cn = New adodb.Connection     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _      "Data Source= C:\Mikes\Loto\Lotto.mdb"     cn.Open     Set rs = New adodb.Recordset     rs.Open "SELECT Count (Zero) AS cnt FROM CASH3", cn, adOpenKeyset, adLockPessimistic, adCmdText     lblZero.Caption = rs.Fields("cnt").Value     rs.Close     rs.Open "SELECT Count (One) AS cnt FROM CASH3", cn, adOpenKeyset, adLockPessimistic, adCmdText     lbl1(1).Caption = rs.Fields("cnt").Value     rs.Close

Thanks

[Edited by mhpate on 22-04-2007 at 04:49 PM GMT]

Hello. Your code seem to be OK. My only remark is there has not to be a space after the Count keyword in the SQL instruction, I mean the correct syntax would be "SELECT Count(Zero) AS ....", but you say it works on the field Zero, so I argue it isn't affected by it, (only, I suggest you to open the recordset object with the ForwardOnly/ReadOnly option parameters, that's faster).
I'd suggest you also to check if the fieldname ("One") is correct, but in such a simple case like this it should to be correct of course.
Is not much, sorry.

____________________________
Real Programmer can count up to 1024 on his fingers

The space was it.
Thank you