type mismatch on getting time defference

Question

kimangel 13 Junior Poster in Training

11 Years Ago

Hi experts, I have a type mismatch error in getting time difference from database. Below are my codes.

Private Sub cmdSearch_Click()

Dim sTrID As String
Dim Total As Integer

Dim xhours As String
Dim Intime As String
Dim Outtime As String

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 Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh

rs!TimeIn = Intime
rs!Timeout = Outtime

xhours = Timediff(Intime, Outtime)

lblTotal_Hours.Caption = xhours

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

Public Function Timediff(ByVal time1 As String, ByVal time2 As String) As String

Dim MinsDiff, TheHours As String

MinsDiff = DateDiff("n", time1, time2)

MinsDiff = IIf(MinsDiff < 0, MinsDiff + 1440, MinsDiff)
TheHours = Format(Int(MinsDiff / 60), "00")
MinsDiff = Format(MinsDiff Mod 60, "00")
Timediff = TheHours & ":" & MinsDiff
End Function

visual-basic

4 Contributors
26 Replies
314 Views
4 Days Discussion Span
Latest Post 11 Years Ago Latest Post by QVeen72

rishif2 57 Posting Whiz in Training

11 Years Ago

post your code including connection string like con.open .........
and dont forget to mention the place where you are creating connection (like module , event etc)

QVeen72 104 Posting Shark

11 Years Ago

Hi,

First Line, Declare RS as New..
20th Line, remove Exit Sub
and no need of ADO.. DataControl..

Check the modified code:

Private Sub Command2_Click()
    Dim rs1 As New ADODB.Recordset
    Dim rs As New ADODB.Recordset
    Dim sTrID As String
    Dim Total As Integer
    Dim xhours As String
    Dim Intime As String
    Dim Outtime As String
    '
    sTrID = txtSearch.text
    Set rs = Nothing
    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
        rs.Close
        Set rs = Nothing
        rs.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'", Cn, 2, 3
        If Not rs.EOF Then
            Intime = rs!TimeIn
            Outtime = rs!TimeOut
            xhours = Timediff(Intime, Outtime)
            set rs1 = Nothing
            rs1.Open "Select Sum([xhours])as totalxhours from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2, 3
            If Not rs1.EOF Then
                lblTotal_Hours.Caption = rs1!totalxhours
            End If
            rs1.Close
            Set rs1 = Nothing
            rs.Close
            Set rs = Nothing
        End If
    Else
        MsgBox "No records found! ", vbExclamation, "DailyRecords"
    End If
End Sub
Public Function Timediff(ByVal time1 As String, ByVal time2 As String) As String
    Dim MinsDiff, TheHours As String
    MinsDiff = DateDiff("n", time1, time2)
    MinsDiff = IIf(MinsDiff < 0, MinsDiff + 1440, MinsDiff)
    TheHours = Format(Int(MinsDiff / 60), "00")
    MinsDiff = Format(MinsDiff Mod 60, "00")
    Timediff = TheHours & ":" & MinsDiff
End Function

Regards
Veena

Edited 11 Years Ago by QVeen72

QVeen72 104 Posting Shark

11 Years Ago

Hi,

Try this :

rs.Open "Select Count(TimeOut-TimeIn) as xtotal from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2,3

Regards
Veena

AndreRet 526 Senior Poster

11 Years Ago

This question has been answered long ago. If you have any other errors or questions, you need to open a new thread, thanx.

QVeen72 104 Posting Shark

11 Years Ago

Hi,

Sorry, it should be Sum() not Count...

rs.Open "Select Sum(TimeOut-TimeIn) as xtotal from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2,3

Regards
Veena

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rishif2 57 Posting Whiz in Training · Answer 1 · 2013-03-07T06:11:04+00:00

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

Now close rs and once again open it , so type the following

        rs.Close
        set rs = Nothing
        'now open the open rs
        rs.open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'",cn,2,3

And alose replace

rs!TimeIn = Intime
rs!Timeout = Outtime

with

Intime=rs!TimeIn 
Outtime=rs!Timeout

And Finally dont forget to close the recordset and the connection

Hope this helps you

kimangel 13 Junior Poster in Training · Answer 2 · 2013-03-07T06:28:39+00:00

Sir, it errors on Intime = rs!TimeIn and it says, item cannot be found in the collection corresponding to the requested name or ordinal."

rishif2 57 Posting Whiz in Training · Answer 3 · 2013-03-07T06:33:35+00:00

i've edited my reply on the post , did you try that . . .

kimangel 13 Junior Poster in Training · Answer 4 · 2013-03-07T07:06:45+00:00

Sir, the above codes retuns an empty value.

Private Sub cmdSearch_Click()

Dim sTrID As String
Dim Total As Integer

Dim xhours As String
Dim Intime As String
Dim Outtime As String

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

rs.Close
Set rs = Nothing
Exit Sub

sTrID = txtSearch.Text

rs.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo  = '" & sTrID & "'", cn, 2, 3

Intime = rs!TimeIn
Outtime = rs!TimeOut

xhours = Timediff(Intime, Outtime)

lblTotal_Hours.Caption = xhours

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

rishif2 57 Posting Whiz in Training · Answer 5 · 2013-03-07T07:12:06+00:00

remove Exit Sub at line 21 And then try to run the code

kimangel 13 Junior Poster in Training · Answer 6 · 2013-03-07T07:26:25+00:00

sir when i removed exit sub, the error is 'object variable or with block variable not set.'

rishif2 57 Posting Whiz in Training · Answer 7 · 2013-03-07T07:42:58+00:00

i don't see any error in your so
try following code

Private Sub cmdSearch_Click()
Dim sTrID As String
Dim Total As Integer
Dim xhours As String
Dim Intime As String
Dim Outtime As String
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
rs.Close
Set rs = Nothing
rs.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo  = '" & sTrID & "'", cn, 2, 3
Intime = rs!TimeIn
Outtime = rs!TimeOut
xhours = Timediff(Intime, Outtime)
lblTotal_Hours.Caption = xhours
rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

kimangel 13 Junior Poster in Training · Answer 8 · 2013-03-07T07:50:26+00:00

same error sir and it points to

rs.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo  = '" & sTrID & "'", cn, 2, 3

the error is 'object variable or with block variable not set.

rishif2 57 Posting Whiz in Training · Answer 9 · 2013-03-07T07:55:33+00:00

what about connection (cn) object
what code for connection and on what event you wrote the code
and at the beginning use the code to initialise connection and at last dont forget to close connection object

AndreRet 526 Senior Poster · Answer 10 · 2013-03-07T08:05:33+00:00

The error means that rs has not been assigned. you need to set a new type for rs...

dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset

if that doesn't solve your problem, it lies with cn. do the same for cn...

rishif2 57 Posting Whiz in Training · Answer 11 · 2013-03-07T08:08:16+00:00

also add code at else part

Else
if rs.State=1 then rs.Close : set rs=Nothing
MsgBox "No records found! ", vbExclamation, "DailyRecords"

kimangel 13 Junior Poster in Training · Answer 12 · 2013-03-07T12:17:48+00:00

Hi Sir, I still not able to resolve this. The same error occurs? Can I ask your favor, if you have something to suggest or other option which gives the same results?

Dim rs As ADODB.Recordset
Dim sTrID As String
Dim Total As Integer

Dim xhours As String
Dim Intime As String
Dim Outtime As String

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

rs.Close
Set rs = Nothing
Exit Sub
rs.Open "Select Sum([xhours])as totalxhours from Time_In where Employees_IdNo  = '" & sTrID & "'", cn, 2, 3

Ado.RecordSource = "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'"

Intime = rs!TimeIn
Outtime = rs!TimeOut

xhours = Timediff(Intime, Outtime)

lblTotal_Hours.Caption = rs!totalxhours


rs.Close
Set rs = Nothing
Else
If rs.State = 1 Then rs.Close: Set rs = Nothing
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub
Public Function Timediff(ByVal time1 As String, ByVal time2 As String) As String

Dim MinsDiff, TheHours As String

MinsDiff = DateDiff("n", time1, time2)

MinsDiff = IIf(MinsDiff < 0, MinsDiff + 1440, MinsDiff)
TheHours = Format(Int(MinsDiff / 60), "00")
MinsDiff = Format(MinsDiff Mod 60, "00")
Timediff = TheHours & ":" & MinsDiff
End Function

AndreRet 526 Senior Poster · Answer 13 · 2013-03-07T13:09:30+00:00

Where is you connection cn being opened? You need to have the connection open before you open the recordset...

dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Open..... ''rest of connection code here

''NOW follows rs - dim rs as adodb.recordset etc..

kimangel 13 Junior Poster in Training · Answer 14 · 2013-03-09T15:25:58+00:00

Hi Veena, I tried your revision but it will returns empty.

Hi Sir rishift2/Andre here's my code from module

Sub main()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "provider=Microsoft.jet.oledb.4.0; data source=" & (App.Path & "\automatedpayrollsystem.mdb") & "; persist security info  = false"
cn.Open
Form21.Show

End Sub

-------

Dim rs1 As New ADODB.Recordset
Dim sTrID As String
Dim Total As Integer

Dim xhours As String
Dim Intime As String
Dim Outtime As String

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
rs.Close
Set rs = Nothing
Exit Sub
rs.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2, 3

If Not rs.EOF Then

Intime = rs!TimeIn
Outtime = rs!TimeOut
xhours = Timediff(Intime, Outtime)
Set rs1 = Nothing
rs1.Open "select sum([xhours])as totalhours from Time_In where employees_IdNo = '" & sTrID & "'", cn, 2, 3

If Not rs1.EOF Then

lblTotal_Hours.Caption = rs1!totalhours
End If

rs1.Close
Set rs1 = Nothing
rs.Close
Set rs = Nothing
End If

Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub
Public Function Timediff(ByVal time1 As String, ByVal time2 As String) As String

Dim MinsDiff, TheHours As String

MinsDiff = DateDiff("n", time1, time2)

MinsDiff = IIf(MinsDiff < 0, MinsDiff + 1440, MinsDiff)
TheHours = Format(Int(MinsDiff / 60), "00")
MinsDiff = Format(MinsDiff Mod 60, "00")
Timediff = TheHours & ":" & MinsDiff
End Function

rishif2 57 Posting Whiz in Training · Answer 15 · 2013-03-09T17:12:19+00:00

why are you putting EXIT SUB

you know , after putting exit sub , your further code wont be evaluated

and also put table structure for the table named Time_In

kimangel 13 Junior Poster in Training · Answer 16 · 2013-03-11T04:00:42+00:00

Ok sir, thanks. exit sub is now removed and I inserted 'Set rs = New ADODB.Recordset' on line 31
and the error now is 'no value given to one or more parameter' points in line 39.

table Time In Datetype: DateIn = date/time,TimeIn = date/time,TimeOut = date/time

QVeen72 104 Posting Shark · Answer 17 · 2013-03-11T07:25:03+00:00

Hi,

Does your table "Time_In" has column name "XHours" ....?
Have You declared "Dim RS As New ADODB.RecordSet".. at the beginning..?

Regards
Veena

kimangel 13 Junior Poster in Training · Answer 18 · 2013-03-11T07:53:17+00:00

Hello Veena, it doesnt have Xhours column, xhours is a total hours of work in a day and am just trying to sum up the number of hours worked for certain period.

Yes, i already added the "Dim RS As New ADODB.RecordSet" at the beggining

rishif2 57 Posting Whiz in Training · Answer 19 · 2013-03-11T09:07:53+00:00

if the table does not contain the field(Xhours) then why using following :-

rs1.Open "select sum([xhours])as totalhours from Time_In where employees_IdNo = '" & sTrID & "'", cn, 2, 3

kimangel 13 Junior Poster in Training · Answer 20 · 2013-03-11T09:22:00+00:00

sir, i thought that could help add the hours work? example, when i have a 3 entries of timein and out in a table and i would like to sum up the hours work?

rishif2 57 Posting Whiz in Training · Answer 21 · 2013-03-11T10:12:27+00:00

by using
rs1.Open "select sum([xhours])as totalhours from Time_In where employees_IdNo = '" & sTrID & "'", cn, 2, 3
you can't do that because there is no field like xhours in you table.

But now I am gonna replying to the original post you made . And now try the following updated code .

first declare cn , rs , rs1 at general declaration level(at module) so write the following

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs1 As New ADODB.Recordset

now on the main procedure

    Sub main()
    cn.ConnectionString = "provider=Microsoft.jet.oledb.4.0; data source=" & (App.Path & "\automatedpayrollsystem.mdb") & "; persist security info  = false"
    cn.Open
    Form21.Show
    End Sub

now on the cmdSearch_Click() event

    Private Sub cmdSearch_Click()
    Dim sTrID As String
    Dim xtotal As Integer
    Dim xhours As String
    Dim Intime As String
    Dim Outtime As String
    sTrID = txtSearch.Text
    rs.Open "Select Count(DateIn) as xtotal from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2,3
    If Not rs.EOF Then
    lblTotal_Days.Caption = rs!xtotal
    rs1.Open "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'", cn, 2, 3
    Intime = rs!TimeIn
    Outtime = rs!TimeOut
    rs1.Close
    Set rs1=Nothing
    xhours = Timediff(Intime, Outtime)
    lblTotal_Hours.Caption = xhours
    rs.Close
    Set rs = Nothing
    Else
    MsgBox "No records found! ", vbExclamation, "DailyRecords"
    rs.Close
    Set rs = Nothing
    End If
    cn.Close
    Set cn = Nothing

Now if this works then add something additional functionality otherwise check error at this level