I am trying to get all the records stored in a Table between two dates. The date values are listed in two textboxes called txtDate1 and txtDate2. The dates are stored in the SQLite table in the format of… 2013-08-28. The code I am using is listed below but no records are returned in the query even though records are recorded between these dates.
Dim sqlook as String
sqlook="SELECT * FROM MeetingAT WHERE UPPER(Date) Between '" + uppercase(txtDate1.Text) + "' And UPPER(Date) = '" + uppercase(txtDate2.Text) + "'"
rs=Roster.SQLSelect(sqlook)
txtMeetings.Text= format(rs.RecordCount,"0")
Can anyone see what may be wrong with my code? Any help would be greatly appreciated.
I don’t think you need the uppercase statements. You also need to ensure that the dates in the text fields is formatted correctly before blindly using them.
FWIW, when searching a datetime field in a MySQL database I append a time parameter like so:
sDat = "t.created_on BETWEEN '" + srchStartDate.SQLDate + " 00:00:00.000' AND '"
sDat = sDat + srchEndDate.SQLDate + " 23:59.59.997'"
// *** note manually inserted times above to ensure we get full results
You can inadvertently omit results if you forget this. Don’t know if it applies to SQLIte, but I’ll post it here for (my…) future reference.