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..
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..
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.
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?
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:
VB.NET:
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.
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:
VB.NET:
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))"
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.
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..
VB.NET:
' 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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.