Time only field

[g4naq]

I have a file I am reading which contains the time in HH:mm format. I need to use this to query against an MS Access column which is defined as Date/Time but only contains the Time.

I would appreciate some help on how do I convert the incoming value from a string to a Time I can use in an SQL SELECT to find a match on the time? I have tried various FORMAT options without success..

Thanks

 

[jmcilhinney]

Are you looking for an exact match or to create a range based on min and/or max time? For an exact match, it's OK to use text. For a range, you'll need to use actual time values, which is a but more complex.

 

[g4naq]

Thanks for responding.. at present I am not expecting an exact match because the data in the file is from some else so the time will be based on the time on their PC / or entered... (could be + or - a few minutes which is a pain).. and the time in the database is when I logged the data.

I suspect I will need to look for a match + or - 5 minutes..

so I can just use the string "HH:mm" for an exact match on the database field?

what do you suggest if I need to use the time + or - 5 mins?

 

[jmcilhinney]

There actually is no such thing as a time-only column in Access. You create a column of type Date/Time and it contains both date and time. In the column properties, you can select a Format and specify date, time or both, but that only determines what is displayed within the Access application itself. The database still contains both the date and time though, so you have to account for that in your queries.

If you want to compare just times then you need to actually extract the time from the values in the database for comparison or else you need to make sure that the date part of the values in the database are the same as the data part of the value you pass in. If you enter just a time in Access itself then the date part defaults to #12/30/1899# but if you save the data from your application then the date part will be whatever you save.

It seems to me that the best option for you is to ignore the date parts, just in case there are some discrepancies. With that in mind, here's an example that will take a time string in "HH:mm" format and retrieve records that have a time value within 5 minutes of that either way:

Private Function GetRecordsWithinFiveMinutes(time As String) As DataTable
    Dim sql = "SELECT *
               FROM MyTable
               WHERE TimeSerial(Hour(TimeColumn), Minute(TimeColumn), Second(TimeColumn)) >= DateAdd(""n"", -5, TimeValue(@Time1))
               AND TimeSerial(Hour(TimeColumn), Minute(TimeColumn), Second(TimeColumn)) <= DateAdd(""n"", 5, TimeValue(@Time2))"
    Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0", .DataSource = "|DataDirectory|\Database1.accdb"}

    Using adapter As New OleDbDataAdapter(sql, builder.ConnectionString)
        With adapter.SelectCommand.Parameters
            .Add("@Time1", OleDbType.VarChar).Value = time
            .Add("@Time2", OleDbType.VarChar).Value = time
        End With

        Dim table As New DataTable

        adapter.Fill(table)

        Return table
    End Using
End Function

The first condition in the WHERE excludes records that are more than 5 minutes before the specified time and the second excludes records that are more than 5 minutes after. You have to add the same value using two different parameters because the ACE provider doesn't actually support named parameters. You can provide names but they are ignored and positions used, so you have to add the same number of parameters to the command as are used in the SQL code and in the same order.

Note that this will fail if the time string doesn't contain a valid time. If that's a possibility then you ought to at validate in your VB code first.

Note that I haven't actually tested this code but I have tested parts of the SQL to make sure that the functions used do work as expected so I'm fairly confident that it will work as is.

 

[g4naq]

There actually is no such thing as a time-only column in Access. You create a column of type Date/Time and it contains both date and time. In the column properties, you can select a Format and specify date, time or both, but that only determines what is displayed within the Access application itself. The database still contains both the date and time though, so you have to account for that in your queries.

If you want to compare just times then you need to actually extract the time from the values in the database for comparison or else you need to make sure that the date part of the values in the database are the same as the data part of the value you pass in. If you enter just a time in Access itself then the date part defaults to #12/30/1899# but if you save the data from your application then the date part will be whatever you save.

It seems to me that the best option for you is to ignore the date parts, just in case there are some discrepancies. With that in mind, here's an example that will take a time string in "HH:mm" format and retrieve records that have a time value within 5 minutes of that either way:

Private Function GetRecordsWithinFiveMinutes(time As String) As DataTable
    Dim sql = "SELECT *
               FROM MyTable
               WHERE TimeSerial(Hour(TimeColumn), Minute(TimeColumn), Second(TimeColumn)) >= DateAdd(""n"", -5, TimeValue(@Time1))
               AND TimeSerial(Hour(TimeColumn), Minute(TimeColumn), Second(TimeColumn)) <= DateAdd(""n"", 5, TimeValue(@Time2))"
    Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0", .DataSource = "|DataDirectory|\Database1.accdb"}

    Using adapter As New OleDbDataAdapter(sql, builder.ConnectionString)
        With adapter.SelectCommand.Parameters
            .Add("@Time1", OleDbType.VarChar).Value = time
            .Add("@Time2", OleDbType.VarChar).Value = time
        End With

        Dim table As New DataTable

        adapter.Fill(table)

        Return table
    End Using
End Function

The first condition in the WHERE excludes records that are more than 5 minutes before the specified time and the second excludes records that are more than 5 minutes after. You have to add the same value using two different parameters because the ACE provider doesn't actually support named parameters. You can provide names but they are ignored and positions used, so you have to add the same number of parameters to the command as are used in the SQL code and in the same order.

Note that this will fail if the time string doesn't contain a valid time. If that's a possibility then you ought to at validate in your VB code first.

Note that I haven't actually tested this code but I have tested parts of the SQL to make sure that the functions used do work as expected so I'm fairly confident that it will work as is.

Thanks for the rely comprehensive response! I have included my version of the code incorporating the SQL from your response. I have this working with slightly different syntax...

sql = "SELECT L_CALL, L_QSONR FROM LOGBOOK WHERE L_CALL = @callsign AND L_DATE= @qsodate AND L_BANDID = @bandid AND L_MODEID = @modeid
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) >= DateAdd(""n"", -5, TimeValue(@Time1))
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) <= DateAdd(""n"", 5, TimeValue(@Time2))"

cmd.Connection = DBconnection
cmd.CommandText = sql
cmd.Parameters.AddWithValue("@callsign", txtCALL)
cmd.Parameters.AddWithValue("@qsodate", txtSLDate)
cmd.Parameters.AddWithValue("@bandid", intBANDID)
cmd.Parameters.AddWithValue("@modeid", intMODEID)
cmd.Parameters.AddWithValue("@Time1", "19:50")
cmd.Parameters.AddWithValue("@Time2", "19:55")


I just need to create the TIme1 and Time2 values based on the incoming date from the file. Really appreciate your help...

 

[jmcilhinney]

You should be using the same time for both the @Time1 and @Time2 parameters. The SQL code uses DateAdd to subtract 5 minutes from that value and to add 5 minutes to that value, giving you a 10 minute range that is centred on the value you provide. The idea is that you're reading that one value from the file and you pass that one value into the query and the SQL code takes care of the rest.

 

[g4naq]

You should be using the same time for both the @Time1 and @Time2 parameters. The SQL code uses DateAdd to subtract 5 minutes from that value and to add 5 minutes to that value, giving you a 10 minute range that is centred on the value you provide. The idea is that you're reading that one value from the file and you pass that one value into the query and the SQL code takes care of the rest.

Thanks I can see what you mean! should have thought a bit longer... may I ask another query based on this... this is the 1st query I run, there is a 2nd query which is exactly the same other than I am not checking the time i.e. a match on the same day but time is out of the 5 minute ranges..

For info I have not written any Access related code until today using VB.Net..

When I run the 2nd query is this appropriate? I have included the whole block this time .. I am not sure whether I need to setup the parameters again for instance..

      ' Read Database - match on Call, Date, Band, Mode and Time +/- 5 minutes
                        Dim cmd As New OleDb.OleDbCommand
                        sql = "SELECT L_CALL, L_QSONR FROM LOGBOOK WHERE L_CALL = @callsign AND L_DATE= @qsodate AND L_BANDID = @bandid AND L_MODEID = @modeid
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) >= DateAdd(""n"", -5, TimeValue(@qsotime1))
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) <= DateAdd(""n"", 5, TimeValue(@qsotime2))"

                        cmd.Connection = DBconnection
                        cmd.CommandText = sql
                        cmd.Parameters.AddWithValue("@callsign", txtCALL)
                        cmd.Parameters.AddWithValue("@qsodate", txtSLDate)
                        cmd.Parameters.AddWithValue("@bandid", intBANDID)
                        cmd.Parameters.AddWithValue("@modeid", intMODEID)
                        cmd.Parameters.AddWithValue("@qsotime1", txtQSOTime)
                        cmd.Parameters.AddWithValue("@qsotime2", txtQSOTime)

                        Dim SLdbReader As OleDbDataReader = cmd.ExecuteReader()

                        If (SLdbReader.HasRows) Then
                            'Exact match
                            MatchFound = True
                            intMatch += 1
                            MatchCount.Text = intMatch
                            'Retrieve data from SQL query
                            While SLdbReader.Read
                                txtSLcall = SLdbReader(0).ToString
                                txtSLQSONr = SLdbReader(1).ToString
                                intSLQSONr = Val(txtSLQSONr)
                            End While
                            'Update match
                            Write_Match()
                        Else

                            MatchFound = False
                        End If
                        SLdbReader.Close()
                        ' Try for close match

                        If (Not MatchFound) Then
                            'use alternative query & try again without QSO Time
                            sql = "SELECT L_CALL, L_QSONR FROM LOGBOOK WHERE L_CALL = @callsign AND L_DATE= @qsodate AND L_BANDID = @bandid AND L_MODEID = @modeid"
                            cmd.Parameters.AddWithValue("@callsign", txtCALL)
                            cmd.Parameters.AddWithValue("@qsodate", txtSLDate)
                            cmd.Parameters.AddWithValue("@bandid", intBANDID)
                            cmd.Parameters.AddWithValue("@modeid", intMODEID)
                            Dim SLdbReader2 As OleDbDataReader = cmd.ExecuteReader()

                            If (SLdbReader2.HasRows) Then
                                'Close match found
                                MatchFound = True
                                intClose += 1
                                CloseCount.Text = intClose
                                'Retrieve data from SQL query
                                While SLdbReader2.Read
                                    txtSLcall = SLdbReader2(0).ToString
                                    txtSLQSONr = SLdbReader2(1).ToString
                                    intSLQSONr = Val(txtSLQSONr)
                                End While
                                'Update match
                                Write_Close_Match()
                            Else
                                'No match found
                                IntNoMatch += 1
                                NoMatchCount.Text = IntNoMatch
                                Write_No_Match()
                            End If
                            SLdbReader2.Close()
                        End If