Date comparison and delimiters 1

[RaceStats]

I've been banging my head against this for the past few days and can't get past it. I'm using VB6 to access an Access DB. All I'm trying to do is compare a tables date field to 2 variables that contain dates. I've tried numerous delimiters and syntax and I either error out or the result dataset is empty.

Below is the basic code that defines and fills everything.

' Code below creates the temp table.

objCon.Execute ("CREATE TABLE [tblTempDriverData] (DriverID Long, RaceDate Date, Points Long)")

objCon.Execute ("CREATE TABLE [tblAvgLast3] (DriverID Long, Last3 Long)")

' Define variables
    Dim sStartDate As Date
    Dim sOldDate As Date
    Dim sNewDate As Date
    Dim sFinishDate As Date
    Dim iAvgCount As Integer
    Dim iNumRaces As Integer
    Dim sTempAvgTable As String
    Dim sTempAvgField As String
    
    sFinishDate = Format(Now(), "Short Date")
    sStartDate = Format(Now(), "Short Date")
    sOldDate = Format(Now(), "Short Date")
    sNewDate = Format(Now(), "Short Date")

' Fill the Temp Driver Data Table
    objCon.Execute ("INSERT INTO tblTempDriverData (DriverID, RaceDate, Points) " & _
    "SELECT R.DriverID, R.ResultDate, R.ResultPoints " & _
    "FROM ((Results R INNER JOIN Drivers D ON R.DriverID = D.DriverID) " & _
    "INNER JOIN Events E ON R.EventID = E.EventID) " & _
    "INNER JOIN Tracks T ON E.TrackID = T.TrackID " & _
    "WHERE Year(R.ResultDate) > " & CStr(iYear) & " AND " & _
    "T.TrackTypeID = (SELECT TrackTypeID FROM Tracks WHERE TrackID = " & CStr(iTrackID) & ") " & _
    "ORDER BY R.ResultDate DESC")

' Now figure out the date range to be selected
    strSQL = "SELECT RaceDate FROM  tblTempDriverData ORDER BY RaceDate DESC"
       
    Set rs = CreateObject("ADODB.recordset")
    rs.Open strSQL, objCon
    
    sFinishDate = (rs.Fields(0))

    Do While Not rs.EOF()

        sNewDate = (rs.Fields(0))
        
        If sNewDate = sOldDate Then
            'Do Nothing
        Else
            sOldDate = sNewDate
            iAvgCount = iAvgCount + 1
            
        End If
        
         If iAvgCount > 3 Then
            sStartDate = (rs.Fields(0))
            Exit Do
        End If
        
        rs.MoveNext
    Loop

' Now fill tblAvgLast3 and calculate the 3 race average
' This is where I get the errors

    objCon.Execute ("INSERT INTO tblAvgLast3 (DriverID, Last3) " & _
        "SELECT T.DriverID, AVG(T.Points) " & _
        "FROM tblTempDriverData T " & _
        "WHERE T.RaceDate BETWEEN #sStartDate# AND #sFinishDate# " & _
        "GROUP BY T.DriverID")

The above code returns a syntax error on the BETWEEN statement. All of the data is there. Both sStartDate and sFinishDate contain the correct information and when you look at the table data it all looks like it should sync up. My problem is obviously the interpretation of the dates.

I've tried converting the variables to string " & Cstr(variable) & " and get empty datasets. I've tried various delimiters and and get syntax errors.

Any thoughts or help would be greatly appreciated.

 

[PHV]

Have you tried this ?
"WHERE T.RaceDate BETWEEN #" & sStartDate & "# AND #" & sFinishDate & "# " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RaceStats]

PHV

Awesome, that worked. I knew it was something like that but I couldn't figure it out.

Thanks a ton for the help.