sql query counting fields returns 0 value

[kimangel]

Hi Experts, my sql query below returns 0 value, though I have entries in my data base. Please help me check my codes below;

Private Sub cmdSearch_Click()

Dim sTrID As String

sTrID = txtSearch.Text
Dim Total As Integer

Set rs = New ADODB.Recordset
rs.Open "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

If Not rs.EOF Then

lblTotal_Days.Caption = Total
Ado.RecordSource = "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh



rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

[AndreRet]

This is how I would've done it, I CONTROL the code then...

Private Sub cmdSearch_Click()
Dim sTrID As String
Dim xTotal As Integer ''DO NOT USE TOTAL, IT IS A VB RESERVED WORD...

sTrID = txtSearch.Text
xTotal = 0

Set rs = New ADODB.Recordset
rs.Open "Select Date_In, sTrID from Time_In where Employees_IdNo = '" & sTrID & "' AND Date_In > 0", cn, adOpenKeyset, adLockPessimistic

Do While Not rs.EOF = True
    xTotal = xTotal + 1
Loop

lblTotal_Days.Caption = xTotal

xTotal = 0
rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

[rishif2]

i think the following statment is producing problem

lblTotal_Days.Caption = Total

so try this

lblTotal_Days.Caption = rs.Fields(0) & " records found"

hope this helps you . . .

[rishif2]

if you dont want to use above code then you can also work with alias (as) and for this you must use total with rs, so now you code will look like as:-

Private Sub cmdSearch_Click()
Dim sTrID As String
sTrID = txtSearch.Text
Dim Total As Integer
Set rs = New ADODB.Recordset
rs.Open "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic
If Not rs.EOF Then
lblTotal_Days.Caption = rs!Total
Ado.RecordSource = "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh
rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

hope this helps you . . .

[kimangel]

Hi experts, tnx for reply. What I mean here is I'm counting the dateIn to get the number of days work of an employee?

[kimangel]

Thank you rishift2, I tried your code and it works.

[rishif2]

if the question got the solution then mark the post as solved

happy coding

[kimangel]

Hi rishift, can I ask one more? Pls!!! I added a code on the form above, computing the total hours work from the field TimeIn and TimeOut. I got some error! Please help me correct it. Many thanks!

Private Sub cmdSearch_Click()

Dim sTrID As String
Dim Total As Integer

Dim lvalues1 As Long
Dim xtimein As Long
Dim xtimeout As Long

sTrID = txtSearch.Text

Set rs = New ADODB.Recordset
rs.Open "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic


If Not rs.EOF Then
lblTotal_Days.Caption = rs!Total

Ado.RecordSource = "Select * from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh

rs!timein = xtimein
rs!timeout = xtimeout

lvalues1 = Gettimedifference(xtimein, xtimeout)

lblTotal_Hours.Caption = lvalues1

rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

[rishif2]

this is wholly a different question and you should create another post for it
and there we will try to solve the problem
so post your question as new thread including function or procedure defination and mark this post as solved

[kimangel]

k thank you sir.