I am trying to display all the records from an SQLite log file starting with the last record on a specified date and ending with the last record on an earlier date. I created a date object (dTest), set its date to the earliest date and its time to 23:59:59. As I loop through the recordset I test for the last recorded record for that date (the first one in the recordset for that date) with
if rs.Field("DateTime").StringValue < dTest.SQLDateTime Then LastToShow = True
With the recordset date being “2014-01-25 23:01:04” and aTest.SQLDateTime being “2014-01-25 23:59:59”, I would expect the If statement to be true and set LastToShow to True. It doesn’t happen. LastToShow is still False when the While not rs.EOF loop ends after the first record of 2014-01-25.
I have seen a suggestion that totalseconds be logged in the database instead of SQLDateTime, but that certainly makes it difficult to manage the database with something like SQLiteManager which I find essential for developing a database app. I am looking for suggestions on how to handle this comparison.
Comparing strings doesn’t make sense. You need to make a date from your value in the database and compare this against your second date.
If (rs.Field("DateTime").DateValue.TotalSeconds < dTest.TotalSeconds) Then LastToShow = True
Comparing string is ok, or you could not sort strings.
And if you write
dim a as String=“2014-01-25 23:01:04”
dim b as String=“2014-01-25 23:59:59”
if a<b then MsgBox “OK”
will show OK
So, maybe there is something wrong with dTest or rs.field(“DateTime”) value
You should be using the WHERE & ORDER BY clauses in SQL to limit your recordset to suit your needs. Let the engine do the work.
For example suppose you have ds as the start date & de as the end date as date objects of course. Your sql select statement might look something like
"SELECT FROM WHERE datetime >= ’ + ds.sqldate + “’ AND datetime <= '” + de.sqldate + “’ ORDER BY datetime DESC;”
Now you have a recordset that matches your requirement and you don’t need to check for lasttoshow at all! If you don’t want to include the start & end dates i.e. between and not including then simply remove the = from the statement.
It turned out my original problem was caused by a glitch in my code, Retyping the line cured it. The code to pick out only the first or last was very complex and confusing. Displaying everything with the newest log entries first meant the first log entries were shown last and the last first. I spent most of the weekend trying to get the code straight in my head. Wayne’s suggestion to let the SQL engine to the work was the answer. It required a UNION, but now my recordset includes only the records I need. And it took less than an hour. Thanks to everyone who responded.