I'm trying to get data from a recordset to drop into a spreadsheet. The data needs to fall between today and a specific date (specified from my Input box.

I am having great fun at the moment. The where clause is causing me no end of problems. I put in a date of 03/11/2005 in my input box and it sends records including 27/10/2005 along with any correct records to Excel. I have tried changing the date format to mm/dd/yyyy but it hasn't worked. Here's the code:

Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim rst As Recordset
    Dim iRow As Integer

    Dim dateNOW, dateThen
    Dim txtQryString

    dateNOW = Date
    dateThen = InputBox("Enter the date the report is to run from")
    txtQryString = "1"
    dateThen = CDate(dateThen)
    txtQryString = "=" & CInt(txtQryString)

    Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWkb = objXL.Workbooks.Open("M:\x")
    Set objSht = objWkb.Worksheets("data")

    iRow = 10

    Set rst = CurrentDb.OpenRecordset("SELECT tblDelegate.Title, tblDelegate.FirstName, tblDelegate.Surname, tblDelegate.PCT, tblDelegate.WorkBase, tblDelegate.JobRole, tblDelegate.LogBookNo, tblCourseTimetable.Date, tblAssessmentDetails.Result, tblCourse.Name, tblAssessmentDetails.Mark, tblcourse.typeid " & _
                                 "FROM (tblCourse INNER JOIN tblCourseTimetable ON tblCourse.CourseID = tblCourseTimetable.CourseID) INNER JOIN (tblAttendanceList INNER JOIN (tblAssessmentDetails INNER JOIN (tblDelegate INNER JOIN tblTestSession ON tblDelegate.DelegateID = tblTestSession.DelegateID) ON tblAssessmentDetails.TestSessionID = tblTestSession.TestSessionId) ON (tblDelegate.DelegateID = tblAttendanceList.DelegateID) AND (tblAttendanceList.DelegateID = tblTestSession.DelegateID) AND (tblAttendanceList.OccurrenceID = tblTestSession.OccurrenceID)) ON tblCourseTimetable.OccurenceID = tblAttendanceList.OccurrenceID " & _
                                 "WHERE tblcourse.typeid " & txtQryString & " and tblCourseTimetable.Date >= " & dateThen & ";")
                                

    If rst.EOF = True Then
        objWkb.Close (False)
        objXL.Quit
        MsgBox "There are no records to add", vbOKCancel + vbExclamation, "No Current record"
    Else
        rst.MoveFirst
    End If

    Do While Not rst.EOF
        objSht.Cells(iRow, 1).Value = rst!Date
        objSht.Cells(iRow, 2).Value = rst!WorkBase
        objSht.Cells(iRow, 3).Value = rst!Name
        objSht.Cells(iRow, 4).Value = rst!FirstName & " " & rst!Surname
        objSht.Cells(iRow, 5).Value = rst!JobRole
        objSht.Cells(iRow, 6).Value = rst!PCT
        objSht.Cells(iRow, 7).Value = rst!Mark
        objSht.Cells(iRow, 8).Value = rst!Result

        iRow = iRow + 1
        rst.MoveNext
    Loop

    Set objWkb = Nothing
    Set objXL = Nothing
    rst.Close
    Set objSht = Nothing



Am I making an elementary mistake? or is it more sinister :) The tblcourse.date data type is date and the format is Short Date

__________________
Rob Carter

I am curious why you Dim the first two dates as Variants, and then CDate() later on. I would do this:

Dim dateNOW As Date
Dim dateThen As Date
Dim txtQryString As Long 'or Integer

Also, why are you writing so much SQL in your code? You should package this query first as a regualr query, and then call the query and pass variables/parameters to it. This would help sort out the incorrect date issue.

What is your system date setting? Is 03/11 March 11, or November 3? The thing to do would be to make sure your system time is set to mm/dd/yyyy since Access refers to it for defualt date structure.

HTH

mmcdonal

The system time is set to dd/mm/yyyy

I have tried what you suggested and the query works out fine. However I use so much SQL in my code because I was never sure how to manipulate a query in VBA :)

Here is how my code now reads:

Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim dbManRep As Database
    Dim rst As Recordset
    Dim qryManRep As QueryDef
    Dim iRow As Integer

    Dim dateNOW As Date
    Dim dateThen As Date
    Dim txtQryString As Integer

    dateNOW = Date
    dateThen = InputBox("Enter Date to start reporting on")

    Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWkb = objXL.Workbooks.Open("M:\x")
    Set objSht = objWkb.Worksheets("data")

    iRow = 10

    Set dbManRep = CurrentDb()
    Set qryManRep = dbManRep.QueryDefs("qry_managementreporting")
    Set rst = qryManRep.OpenRecordset()

    If rst.EOF = True Then
        objWkb.Close (False)
        objXL.Quit
        MsgBox "There are no records to add", vbOKCancel + vbExclamation, "No Current record"
    Else
        rst.MoveFirst
    End If

    Do While Not rst.EOF
        objSht.Cells(iRow, 1).Value = rst!Date
        objSht.Cells(iRow, 2).Value = rst!WorkBase
        objSht.Cells(iRow, 3).Value = rst!Name
        objSht.Cells(iRow, 4).Value = rst!FirstName & " " & rst!Surname
        objSht.Cells(iRow, 5).Value = rst!JobRole
        objSht.Cells(iRow, 6).Value = rst!PCT
        objSht.Cells(iRow, 7).Value = rst!Mark
        objSht.Cells(iRow, 8).Value = rst!Result

        iRow = iRow + 1
        rst.MoveNext
    Loop

    Set objWkb = Nothing
    Set objXL = Nothing
    rst.Close
    Set objSht = Nothing

And it says "Missing Parameters, Expected 2" as the error message. I am unsure how to reference the variables in my code inside the query or vice-versa.

After much fiddling and prodding around I finally solved my conundrum.

Thanks for your help. Your suggestion formed the basis of the solution.