Sequel statement error

[JackCrackers]

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

 

[Mihail]

What your SQL return if you use it in Access (as query) ?

 

[JackCrackers]

I will check now

Thank you
John

 

[JackCrackers]

The query returned the same wrong results as the VB 6.0 program

John

 

[Mihail]

First take a look here.
Also you will find other useful link in that thread.
If still can't solve then upload the mdb

 

[JackCrackers]

Hello
The example code in your link solved my problem. The error was caused by date compatibility issues with regional date/time format.

Thanks very much
John

 

[JackCrackers]

Re: Sequel statement error in date comparison additional problems

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
                scryn$ = "Y"

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

 

[Mihail]

Unfortunately, my knowledge in SQL area is very reduced.
Seems that SQL (or only in this case) compare as strings.
Try to format your dates as yyyy/mm/dd.

Hope that other, with a better understanding of SQL will help you here.

 

[JackCrackers]

Thank you

John

 

[JackCrackers]

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