Why is my recordset not finding values on particular dates?
Posted on 2014-11-01
I'm having some strange behaviour occurring when searching if a record exists in a recordset based on 2 matching criteria: an ID (lngID) number and a Date/Time (dateLastSample).
It works most of the time. But fails to find records that I know are there (and consequently ends up duplicating them) for specific dates: e.g 11-Sep-14, 11-Oct-14, 1-Nov-14. The rs.recordcount = 0 for these dates. Surrounding dates seem to be fine, and I haven't gone back further than 11-Sep
I'm forcing the dates into the required SQL mm/dd/yy format.... is there some other peculiarity with dates that I'm missing?
lngID = 39
dateLastSample = #10/15/2014 01:00#
Dim rs as dao.recordset
Set rs = db.OpenRecordset("SELECT * FROM tblOPLOGS_Data WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & Format(CDate(dateLastSample), "mm/dd/yy hh:mm") & "#")
'Add Record if required
If rs.EOF Then 'If no record then
!SampleDateTime = datelastsample
!TGOLevel2 = lngID
Else 'If record exists do nothing
set rs = nothing