Compare system time to an Access table field

[kre1973]

This is an application to remind someone to do things at a certain time of the day. There are sometimes more than one thing the person has to do at a particular time.

So, basically I'm try to alert the user with a popup message box that it's a certain time and to do required things now.

I'm trying to compare a field in an Access database to the current system time using the control Timer.


Example of data in the Access field called: Time_To_Check
8:00:00
8:00:00
8:00:00
8:30:00
9:00:00
9:00:00
10:00:00
10:00:00
10:30:00
11:00:00

The data type for field Time_To_Check in Access is in the Date/Time and the format is: hh:nn:ss

I'm using seconds so the popup will only happen once.

I'm attaching the code with this posting, it's not working.

 Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
  Dim Time_Compare As String = Format(Now, "H:mm:ss")
        If conn.State = ConnectionState.Open Then
            conn.Close()
        Else
            conn.Open()
        End If
        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"

        Using cmd1 As New OleDbCommand(strSQL1, conn)
            cmd1.Parameters.Add("@Time_Compare", OleDbType.Date).Value = Time_Compare
            Dim sdr As OleDbDataReader = cmd1.ExecuteReader

            If (sdr.Read()) Then
                MsgBox("Do checkouts")
            End If
            sdr.Close()

        End Using
        conn.Close()
    End Sub

 

[RiverGuy]

You say you store your data in Access like so 8:00:00. I don't know much about Access anymore, but that seems like a VARCHAR datatype in that there is no date portion--just a time. However, you are creating a String variable to get the current time but you are adding a parameter of Date. That being said, if your datatype in Access is really a string type, change your parameter to a string type.

 

[ZmrAbdulla]

I was just trying your code and some changes gave me the result.

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Dim Conn As New OleDbConnection
        Conn.ConnectionString = GetConnetionString()

        Dim Time_Compare As[b] [COLOR=red] Date[/color][/b] = Format(Now, "H:mm:ss")
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
            [b][COLOR=red]Conn.Open()[/color][/b]
        Else
            Conn.Open()
        End If
        Dim strSQL1 As String = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"

        Using cmd1 As New OleDbCommand(strSQL1, Conn)
            cmd1.Parameters.Add("@Time_Compare", OleDbType.Date).Value = Time_Compare
            Dim sdr As OleDbDataReader = cmd1.ExecuteReader

            If (sdr.Read()) Then
                Me.TextBox1.AppendText("Do checkouts " & Time_Compare & vbCrLf)
                MsgBox("Do checkouts" & Time_Compare)
            Else
                Me.TextBox1.AppendText("Not This time " & Time_Compare & vbCrLf)
            End If
            sdr.Close()

        End Using
        Conn.Close()
    End Sub

Zameer Abdulla