Count

Hello,
I am makeing a program for the Lotto.
I have a Access 2003 database with fields 0-9, would like to count the number of entries in each field and display in label caption.
Form has Labels 0-9.
cmdCount.

VB Code:

rs.Open "Select COUNT (*) FROM Cash3", cn, adOpenKeyset, adLockPessimistic, adCmdText
        With rs
            lblZero.Caption = rs.Fields("Zero").Value
            
   
        End With
    rs.Close

Can anyone help with this?

I usually do:

VB Code:

Dim count as Long
rs.Open "Select COUNT (*) FROM Cash3", cn, adOpenKeyset, adLockPessimistic, adCmdText
count = rs.GetString
rs.Close
MsgBox count

VB Code:

Rs.open "select count(scholarNo) as S from Student_Info",Con,2,3
Msgbox Rs!S

Clarification please... do you need the number of records in that table? If so then solutions were given.

Or do you want to count number of records relative to a given field where the field is not null or zero. You have to clarify rule/s for not counting a given record.field.value in the count; such as null and zero.

I need to count the number of records relative to a given field where the field is not null or zero.
Example- how many times the number 1 has been entered into field One.

This should work:

VB Code:

Rs.Open "SELECT COUNT(*) FROM tablename WHERE One=1", cn

Then, you can either use rs.GetString or set the output "as X" and call it like that.. using the code I posted previously, you just need to add:

VB Code:

count = Rs.GetString
Rs.Close

Try

Select Count(Zero) As RecCount FROM Cash3 WHERE Zero > 0
UNION
Select Count(One) FROM Cash3 WHERE One > 0
UNION
Select Count(Two) FROM Cash3 WHERE Two > 0
etc etc insert other queries for 3-8
UNION
Select Count(Nine) FROM Cash3 WHERE Nine > 0

So first record (value of rs!RecCount at first record) is the count for field zero, second record for field one and so on and so forth. Let us know if it throws an error msg.

Error
Data type mismatch in criteria exepression.

Then we need info regarding your table structure; table name, field names, data type of each field

Also post the erroneous query you used

Data Base = Lotto
Table = Cash3
Field =
Date - Date
zero - Number
One - Number
Two - Number
Three - Number
Four - Number
Five - Number
Six - Number
Seven - Number
Eight - Number
Nine - Number
Doubles - Number
Triples - Number
Order -

VB Code:

Option Explicit
Private cn As ADODB.Connection
Private rs As Recordset
Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, _
  ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Sub cmdCount_Click()
        'just to see if it worked
       rs.Open "Select COUNT (Zero) As RecCount FROM Cash3 WHERE Zero > 0", cn, adOpenKeyset, adLockPessimistic, adCmdText
       With rs
            lblZero.Caption = rs.Fields("Zero")
            
   
        End With
    rs.Close
End Sub
 
Private Sub Form_Load()
    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 = 11
    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 "Cash3", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    rs.MoveFirst
    Do Until rs.EOF = True
    rs.MoveNext
    Loop
    rs.MoveFirst
    Me.MousePointer = 0
    rs.Close
End Sub
Private Sub mnuClear_Click()
    txtDate.Text = ""
    txt0.Text = ""
    txt1.Text = ""
    txt2.Text = ""
    txt3.Text = ""
    txt4.Text = ""
    txt5.Text = ""
    txt6.Text = ""
    txt7.Text = ""
    txt8.Text = ""
    txt9.Text = ""
    txtDoubles.Text = ""
    txtTriples.Text = ""
    txtRandom.Text = ""
    txtOrder = ""
    lblYes.Caption = ""
    txtDate.SetFocus
End Sub
 
Private Sub mnuCount_Click()
   
    rs.Open "Select COUNT (*) FROM Cash3 Where Zero ", cn, adOpenKeyset, adLockPessimistic, adCmdText
       With rs
            lblZero.Caption = rs.Fields("Zero").Value
   
        End With
    rs.Close
End Sub
 
Private Sub mnuEnter_Click()
    rs.Open "Cash3", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        With rs
            .AddNew
            .Fields("Date") = txtDate.Text
            .Fields("Zero") = txt0.Text
            .Fields("One") = txt1.Text
            .Fields("Two") = txt2.Text
            .Fields("Three") = txt3.Text
            .Fields("Four") = txt4.Text
            .Fields("Five") = txt5.Text
            .Fields("Six") = txt6.Text
            .Fields("Seven") = txt7.Text
            .Fields("Eight") = txt8.Text
            .Fields("Nine") = txt9.Text
            .Fields("Doubles") = txtDoubles.Text
            .Fields("Triples") = txtTriples.Text
            .Fields("Order") = txtOrder.Text
            .Update
    rs.Close
    End With
    lblYes.Caption = "YES"
End Sub
 
Private Sub mnuExit_Click()
    End
End Sub

Number

There's nothing wrong with the query, your not using the alias reccount.

VB Code:

Dim sSQL As String
 
sSQL = "Select COUNT (Zero) As RecCount FROM Cash3 WHERE Zero > 0"
sSQL = sSQL & "UNION Select Count(One) FROM Cash3 WHERE One > 0"
 
'just to see if it worked
rs.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
       With rs
            [b]'lblZero.Caption = .Fields("Zero")   'this is your error[/b]
            lblZero.Caption = .fields("RecCount").Value
            .MoveNext
            lblOne.Caption = .Fields("RecCount").Value
        End With

fill in the rest... update sSQL to include union of other fields' count aggregate... update othe labels to display result... don't forget to use alias RecCount and to iterate (.MoveNext) through recordset.

BTW I don't think this will work due to different data types

VB Code:

.Fields("Date") = txtDate.Text
            .Fields("Zero") = txt0.Text
            .Fields("One") = txt1.Text
            .Fields("Two") = txt2.Text
            .Fields("Three") = txt3.Text
            .Fields("Four") = txt4.Text
            .Fields("Five") = txt5.Text
            .Fields("Six") = txt6.Text
            .Fields("Seven") = txt7.Text
            .Fields("Eight") = txt8.Text
            .Fields("Nine") = txt9.Text
            .Fields("Doubles") = txtDoubles.Text
            .Fields("Triples") = txtTriples.Text
            .Fields("Order") = txtOrder.Text

Convert string/text to relevant data type when assigning to a field. sample
.Fields("Zero") = Val(txt0.Text)

I get
Syntax error (missing operator) in query expression 'Zero >UNION
Select Count (One) FROM Cash3 WHERE One > 0'.

There is a simple way to do this ( I lost it when my computer cashed ).
I do not remember using sSQL = sSQL as I am using Access.
Oh well I'll find it, just have to keep looking.

The error you get is caused because you are missing a space:

VB Code:

sSQL = "Select COUNT (Zero) As RecCount FROM Cash3 WHERE Zero > 0"
sSQL = sSQL & "UNION Select Count(One) FROM Cash3 WHERE One > 0"

You would get:

VB Code:

sSQL = "Select COUNT (Zero) As RecCount FROM Cash3 WHERE Zero > [B]0UNION[/B] Select Count(One) FROM Cash3 WHERE One > 0"

So just make a simple adjustement...

VB Code:

sSQL = "Select COUNT (Zero) As RecCount FROM Cash3 WHERE Zero > 0"
sSQL = sSQL &[B] " UNION [/B] Select Count(One) FROM Cash3 WHERE One > 0"

and that's all...