Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 10th, 2005, 07:08 AM
Authorized User
 
Join Date: Nov 2005
Location: Bromsgrove, United Kingdom.
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default Date problems

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
  #2 (permalink)  
Old November 10th, 2005, 09:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #3 (permalink)  
Old November 10th, 2005, 09:54 AM
Authorized User
 
Join Date: Nov 2005
Location: Bromsgrove, United Kingdom.
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

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.
  #4 (permalink)  
Old November 10th, 2005, 10:39 AM
Authorized User
 
Join Date: Nov 2005
Location: Bromsgrove, United Kingdom.
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

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

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL and ISO date problems CallumBeveridge SQL Server 2000 6 August 17th, 2007 02:14 PM
copy date values between date controls Alcapone Javascript How-To 1 April 13th, 2006 03:13 AM
Date problems StevieB SQL Server 2000 6 July 11th, 2005 12:25 PM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.