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.
