Sending data from Access to Excel 2

[doctorswamp]

Hi all

Have working code that send data to Excel from a fixed query in Access. I'd like the query to ask for a date range but get an error.

This code works

****************

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

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\timesheet.xlt")
Set objSht = objWkb.Worksheets("Timesheet")
iRow = 6

Set rst = CurrentDb.OpenRecordset("SELECT TimesheetHistory.HistoryDate," _
& "CompanyData.LTRef, CompanyData.Company, CompanyData.Contact," _
& "TimesheetHistory.Project, CompanyData.CNo, TimesheetHistory.Duration," _
& "TimesheetHistory.Adviser, TimesheetHistory.TimeSheetSource," _
& "TimesheetHistory.Type, TimesheetHistory.Reason, TimesheetHistory.Notes " _
& "FROM TimesheetHistory INNER JOIN CompanyData ON TimesheetHistory.LTRef = CompanyData.LTRef " _
& "WHERE (((TimesheetHistory.HistoryDate) Between #2/1/2006# And #2/28/2006#))" _
& "ORDER BY TimesheetHistory.HistoryDate;")

etc

****************

But when I change the WHERE condition to

Between [Start Date] And [End Date]

it returns

Run-time Error 3061
Too few parameters. Expected 2

Any ideas much appreciated.

Thanks

 

[Remou]

I think you will need either a form with textboxes for these dates, or InputBoxes. I think a form would be best. Then you can say something like:

[tt]& "WHERE (((TimesheetHistory.HistoryDate) Between #" _
& Format(Forms!frmForm!txtStart, "mm/dd/yy") & "# And # " _
& Format(Forms!frmForm!txtEnd, "mm/dd/yy") _
& "# ORDER BY TimesheetHistory.HistoryDate;")[/tt]

I hope I typed that right.

 

[doctorswamp]

Thanks Remou

I think this would work with some changes in structure which I'll have to think about.

The way it works at present is that I build timesheet entries by going to each client's page on a company form, and adding the entries into a subform. I then query the data table behind the subform for all entries between given dates - so it pulls out all companies contacted in the month.

The query uses DatePart to select by month number for the current year. Trying to send the query output to Excel with this or dates Between ... And ... gave the error I mentioned, as if to say you can't use parameters in this way. I could define a fixed query for each month and have 12 buttons to send the data to Excel but this isn't very elegant.

 

[Remou]

If you build the query on the fly using the SQL string shown, you will get prompted. Or use an InputBox:

[tt]SDate = InputBox("Please enter Start Date", "Enter Date", Date)[/tt]

You can then use SDate in your SQL string.

 

[doctorswamp]

Thanks Remou - I think it's nearly there.

Just can't get my head round the query on the fly, and how to work in the Inputbox.

Do you mean that the command button should launch a form that asks for the start and end dates, then use an After Update procedure on the End Date field to run the code that links to Excel?

 

[Remou]

The InputBox stuff does not look great but is very handy where image is not important. You can put InputBoxes anywhere before the SQL string. On the fly queries involve opening an existing query and changing the SQL (easiest) or building the whole thing from scratch. I think the most common way to do what you want is a small dialog form, which seems to be the way you are now going. I would recommend a command button to run the Excel transfer code, as that way you get to check everything first. You can also validate all dates etc, always very useful.

 

[doctorswamp]

Hi Remou

Sorry to be slow but I'm not quite there.

I tried creating to inputboxes for Sdate and Edate but am stumbling on the SQL statement syntax, ie going from say

Between #1/3/2006# and #31/3/2006#

to the equivalent using Sdate and Edate.


As a second attempt I created a form with two unbound textboxes called StartDate and EndDate then tried to call these up in the criterion for the query on which the SQL statement is based. Again couldn't get the syntax right for the date range.

Many thanks

 

[Remou]

Have you tried (?):

SDate = InputBox("Please enter Start Date", "Enter Date", Date)
EDate = InputBox("Please enter End Date", "Enter Date", Date)

Set rst = CurrentDb.OpenRecordset("SELECT TimesheetHistory.HistoryDate," _
& "CompanyData.LTRef, CompanyData.Company, CompanyData.Contact," _
& "TimesheetHistory.Project, CompanyData.CNo, TimesheetHistory.Duration," _
& "TimesheetHistory.Adviser, TimesheetHistory.TimeSheetSource," _
& "TimesheetHistory.Type, TimesheetHistory.Reason, TimesheetHistory.Notes " _
& "FROM TimesheetHistory INNER JOIN CompanyData ON TimesheetHistory.LTRef = CompanyData.LTRef " _
& "WHERE (((TimesheetHistory.HistoryDate) Between #" _
& Format(SDate, "mm/dd/yy") & "# And #" _
& Format(EDate, "mm/dd/yy") _
& "# ORDER BY TimesheetHistory.HistoryDate;")

 

[doctorswamp]

Hi

Have copied and pasted this into procedure to avoid transcription errors. The ran with dates 03/03/06 and 04/04/06 but get

Run-time error '3075'

Syntax error (missing operator) in query expression
'(((TimesheetHistory.HistoryDate) Between #03/03/06# And
#04/04/06# ORDER BY TimesheetHistory.HistoryDate;'

David

 

[JerryKlmns]

& "WHERE (((TimesheetHistory.HistoryDate) Between #" _
& Format(SDate, "yyyy-mm-dd") & "# And #" _
& Format(EDate, "yyyy-mm-dd")_
& "#)) ORDER BY TimesheetHistory.HistoryDate;")

 

[doctorswamp]

Must be losing my mind cos I replied but it hasn't appeared.

Works a treat now, thanks all!

At least it populates my Excel template but with a strange date range.

I've tried combinations of dd/mm/yy and mm/dd/yy etc.

The data goes in as dd/mm/yy so does Format need anything special?