Finding records with a date between 2 given dates

[OceanaPolynom]

Hello


I am writing a program in VB 6.0 using a Access mdb database file. I am trying to retrieve records from a table, where the date of the records falls between 2 given dates from a record in a different table. The code is returning the wrong records. this is an example of the data:

tblResults Reindx     ReCn     ReCh     RePDate     RePactv     RePrumm     ReInt     ReHl     ReSDate     ReEDate     ReStatus     ReBDate     ReDays
16     87     87.01     09/03/2012 22:00:00     100     -49         20     09/03/2012 14:00:00     10/03/2012 08:00:00     Bred     09/03/2012     0

tblRLine RLIndx     RLCn     RLDate     RLTime     RLAc     RLRm     RLLs     RLDays     RLdateTime
66     87     09/03/2012     14:00:00     31     -6     Bred     0     09/03/2012 14:00:00
67     87     09/03/2012     16:00:00     66     -13     Bred     0     09/03/2012 16:00:00
68     87     09/03/2012     18:00:00     85     -15     Bred     0     09/03/2012 18:00:00
69     87     09/03/2012     20:00:00     90     -22     Bred     0     09/03/2012 20:00:00
70     87     09/03/2012     22:00:00     100     -21     Bred     0     09/03/2012 22:00:00
71     87     10/03/2012     00:00:00     97     -34     Bred     0     10/03/2012
72     87     10/03/2012     02:00:00     96     -49     Bred     0     10/03/2012 02:00:00
73     87     10/03/2012     04:00:00     93     -26     Bred     0     10/03/2012 04:00:00
74     87     10/03/2012     06:00:00     84     -36     Bred     0     10/03/2012 06:00:00
75     87     10/03/2012     08:00:00     81     -17     Bred     0     10/03/2012 08:00:00
76     87     04/07/2012     12:00:00     36     -20     Pregnant     116     04/07/2012 12:00:00
77     87     04/07/2012     14:00:00     54     -21     Pregnant     116     04/07/2012 14:00:00
78     87     04/07/2012     16:00:00     49     -19     Pregnant     116     04/07/2012 16:00:00
79     87     04/07/2012     18:00:00     30     -19     Pregnant     116     04/07/2012 18:00:00
80     87     25/07/2012     02:00:00     63     -21     Pregnant     137     25/07/2012 02:00:00
81     87     25/07/2012     04:00:00     82     -31     Pregnant     137     25/07/2012 04:00:00
82     87     25/07/2012     06:00:00     79     -47     Pregnant     137     25/07/2012 06:00:00
83     87     25/07/2012     08:00:00     67     -54     Pregnant     137     25/07/2012 08:00:00
84     87     25/07/2012     10:00:00     60     -34     Pregnant     137     25/07/2012 10:00:00
85     87     02/09/2012     02:00:00     43     8     Open and No Heat     176     02/09/2012 02:00:00
86     87     02/09/2012     04:00:00     73     4     Open and No Heat     176     02/09/2012 04:00:00
87     87     02/09/2012     06:00:00     84     2     Open and No Heat     176     02/09/2012 06:00:00
88     87     02/09/2012     08:00:00     88     -5     Open and No Heat     176     02/09/2012 08:00:00
89     87     02/09/2012     10:00:00     86     -7     Open and No Heat     176     02/09/2012 10:00:00
90     87     02/09/2012     12:00:00     81     -9     Open and No Heat     176     02/09/2012 12:00:00
91     87     02/09/2012     14:00:00     69     -10     Open and No Heat     176     02/09/2012 14:00:00
92     87     02/09/2012     16:00:00     38     -15     Open and No Heat     176     02/09/2012 16:00:00
93     87     29/09/2012     18:00:00     43     -25     Open and No Heat     203     29/09/2012 18:00:00
94     87     29/09/2012     20:00:00     47     -28     Open and No Heat     203     29/09/2012 20:00:00
95     87     29/09/2012     22:00:00     47     -23     Open and No Heat     203     29/09/2012 22:00:00
96     87     30/09/2012     00:00:00     79     -17     Open and No Heat     204     30/09/2012

Here is the code

    ssql = "select * from tblrline where (tblrline.rldatetime>=" & "#" & rstemp!resdate & "#" & ") and (tblrline.rldatetime<=" & "#" & rstemp!reedate & "#) and (tblrline.rlcn=" & thiscow& & ")"
    With rstemp1
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .ActiveConnection = cn1
        .Source = ssql
        .Open
    End With

This code is meant to return records from tblRlines that have dates between tblresults!resdate and tblresults!reedate that is between
tblResults ReSDate
09/03/2012 14:00:00

and

tblResults ReEDate
10/03/2012 08:00:00

There are 10 such records in tblRline from key index=66 until key index =77

Instead 5 records are returned between the dates

tblRLine RLdateTime
29/09/2012 18:00:00

and

tblRLine RLdateTime
30/09/2012 02:00:00

The string used to get the records from tblRlines looks like this :

select * from tblrline where (tblrline.rldatetime>=#09/03/2012 14:00:00#) and (tblrline.rldatetime<=#10/03/2012 08:00:00#) and (tblrline.rlcn=87)

I can't find my mistake

Thank you

John

[June7]

You don't show the query for rstemp recordset.

Is thiscow& a variable? Why is there an extra & in the code? How is thiscow populated? Can simplify concatenation of the sql:

"select * from tblrline where tblrline.rldatetime BETWEEN #" & rstemp!resdate & "# AND #" & rstemp!reedate & "# and tblrline.rlcn=" & thiscow


I built a table and tested an Access query with static criteria and it works:

SELECT tblRLine.RLIndex, tblRLine.RLCn, tblRLine.RLDate, tblRLine.RLTime, tblRLine.RLAc, tblRLine.RLRm, tblRLine.RLLs, tblRLine.RLDays, tblRLine.RLDateTime
FROM tblRLine
WHERE (((tblRLine.RLDateTime) Between #9/3/2012 14:0:0# And #10/3/2012 8:0:0#));


Have you step debugged? Review link at bottom of my post for debugging guidelines.

[OceanaPolynom]

I am checking now

Thank you
John

[OceanaPolynom]

Hello

I changed my sql statement according to yours. In code it looks like this:

select * from tblrline where tblrline.rldatetime BETWEEN #09/03/2012 14:00:00# AND #10/03/2012 08:00:00# and tblrline.rlcn=87

I also ran the same query in Access. In both cases it returned the same wrong results i got before using my query.

The code for rstemp recordset is:

thiscow& = Val(thiscowno$)
ssql = "select * from tblresults where tblresults.recn=" & thiscow& & " order by tblresults.rech"
    With rstemp
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .ActiveConnection = cn1
        .Source = ssql
        .Open
    End With
numberofheats% = rstemp.RecordCount
rstemp.MoveFirst

This query returns 7 records from tblResults, the first record s contents is:

Reindx
ReCn
ReCh
RePDate
RePactv
RePrumm
ReInt
ReHl
ReSDate
ReEDate
ReStatus
ReBDate
ReDays

16
87
87.01
09/03/2012 22:00:00
100
-49


20
09/03/2012 14:00:00
10/03/2012 08:00:00
Bred
09/03/2012
0

The parameters for the query to tblRline are taken from the above. The dates in this db are dd/mm/yyyy. Could this be a source of error

John

[June7]

Yes, that date structure would be a definite issue. Review: http://allenbrowne.com/ser-36.html

[OceanaPolynom]

Hello
Thanks a lot. The problem was caused by by regional date/time setting. This code example from your link solved the problem:

• strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"

The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes.

John

[OceanaPolynom]

Hello

After taking your advice I found the following code to format the date/time variables properly:

Function SQLDate(varDate As Variant) As String
     'Purpose:    Return a delimited string in the date format used natively by JET SQL. 
    'Argument:   A date/time value.     'Note:       Returns just the date format if the argument has no time component, 
    '                or a date/time format if it does. 
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006. 
    If IsDate(varDate) Then 
        If DateValue(varDate) = varDate Then 
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#") 
        Else             SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
         End If 
    End If 
End Function

It seemed to work well and solve the problem. Upon further debugging I found a case where it failed to work as expected. I think it may have something to do with crossing January 1st.

I am using this code to compare the dates:

firstdate = Str(rstemp!resdate)
firstdateminus1 = DateAdd("d", -1, firstdate)
firstdateminus1 = SQLDate(firstdateminus1) 

 lastdate = Str(rstemp!reedate)
 lastdateplus2 = DateAdd("d", 2, lastdate)
 lastdateplus2 = SQLDate(lastdateplus2) 

eventdate = SQLDate(rstemp1!evdate)
If eventdate >= firstdateminus1 And eventdate <= lastdateplus2 Then
 .
.
.

The final values of the date variables in the If statement look like this:

#12/29/2012 14:00:00# #12/31/2012# #01/01/2013 16:00:00#

That is:

If (#12/31/2012# >=#12/29/2012 14:00:00#) And (#12/31/2012# <=#01/01/2013 16:00:00#) Then ...

The first comparison returns True, which is correct. The second comparison, which crosses the New Year, returns False, which is incorrect, and the If statement fails to do its job.

Thank you

John

[June7]

Can't help you there. The expression evaluates True for me.

[OceanaPolynom]

Hello

By trying various combinations I arrived at a solution that seems to work in all cases

CODE
firstdateminus1 = DateAdd("d", -1, rstemp!resdate)
lastdateplus2 = DateAdd("d", 2, rstemp!reedate)
devent = rstemp1!evdate

If (DateDiff("n", devent, firstdateminus1) <= 0) And (DateDiff("n", devent, lastdateplus2) >= 0) Then

.
.
.


Thanks

John