Sending query output to Excel template 4

[doctorswamp]

Hi all

Have working code to send data from a table SampleTimesheet to an Excel template, as at bottom of this message.

I need to do the same thing using data from a query rather than a table. I've adapted the code but get VB errors when I replace this line

Set rst = CurrentDb.OpenRecordset("SampleTimesheet")

with an SQL statement derived from a query which links companyData and TimeSheetHistory tables, asks for a month number then extracts timesheet data.

The adapted version I tried is

Set rst = CurrentDb.OpenRecordset("SELECT TimesheetHistory.Date,
CompanyData.LTRef,
CompanyData.Company,
CompanyData.Contact,
CompanyData.CNo,
TimesheetHistory.Project,
TimesheetHistory.Adviser, TimesheetHistory.TimeSheetSource,
TimesheetHistory.Type,
TimesheetHistory.Reason,
TimesheetHistory.Notes

FROM TimesheetHistory INNER JOIN CompanyData ON TimesheetHistory.LTRef = CompanyData.LTRef

WHERE (((TimesheetHistory.Date)>#4/1/2005#) AND ((DatePart("m",[Date]))=[Enter Month No]))

ORDER BY TimesheetHistory.Date;")

but parts of this immediately come up in red. I've inserted some returns to make it easier to read.

Any suggestions for fixing this will be much appreciated.

----------------------------------

The working code for the simpler version was

Private Sub Timesheet_Click()

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

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

Set rst = CurrentDb.OpenRecordset("SampleTimesheet")

rst.MoveFirst

Do While Not rst.EOF

' Insert the field names and row numbers of the fields to be inserted
objSht.Cells(iRow, 1).Value = rst!Date
objSht.Cells(iRow, 2).Value = rst!LTRef
objSht.Cells(iRow, 3).Value = rst!Company
objSht.Cells(iRow, 4).Value = rst!Contact
objSht.Cells(iRow, 5).Value = rst!Project
objSht.Cells(iRow, 6).Value = rst!CNo
'objSht.Cells(iRow, 7).Value = rst!Duration
'objSht.Cells(iRow, 8).Value = rst!TimeSheetSource
'objSht.Cells(iRow, 9).Value = rst!Project
'objSht.Cells(iRow, 10).Value = rst!Project
'objSht.Cells(iRow, 11).Value = rst!Project



iRow = iRow + 1
rst.MoveNext
Loop

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

End Sub

 

[mp9]

I doubt it likes your use of quotes in the SQL string, i.e. DatePart("m",[Date]) - Access will interpret the quote mark as being the end of your SQL string, hence the error.

Try using either single quotes ('m') or double double quotes (""m"") instead.

http://www22.brinkster.com/accessory/

 

[doctorswamp]

Thanks mp9 but still have problem.

To enlarge, I've copied the SQL direct from a query that works, and it goes in the code above as 4 long lines, starting Set... FROM... WHERE... and ORDER By...

If I delete then retype the last letter of the first line I get 'expected list separator or )', and it and the second line appear in red.

The WHERE line is okay, so the "m" doesn't seem to bother it.

If I delete then retype the last letter of the ORDER line it highlights TimesheetHistory.Date and says Expected end of Statement.

 

[lespaul]

Can you use a parameter in this manner? What parts "immediately come up red".

Leslie

if you select the "Process TGML" link below the posting area you can see how to modify your text to show the red parts

 

[JerryKlmns]

Besides the use of parameters that Leslie points to, this

DatePart("m",[Date])

should be better

DatePart('m',[TimesheetHistory].[Date])

since Date is a reserved word

 

[doctorswamp]

Dear All

Thanks - seem to be converging on the answer.

I've removed the DatePart("m", etc) bit. Then found I had to make one huge long code line by taking out the returns before FROM, WHERE and ORDER, and it works!

Remaining question is how can I break up the line into readable bits, since the usual space + underscore causes errors 'expected list separator or )'

Instead of trying to get it to ask for the month number I think I'll try and construct the first and last days of the current month in the query/SQL.

 

[PHV]

A starting point:
Set rst = CurrentDb.OpenRecordset("SELECT " _
& "TimesheetHistory.Date,CompanyData.LTRef,CompanyData.Company,CompanyData.Contact,CompanyData.CNo," _
& "TimesheetHistory.Project,TimesheetHistory.Adviser,TimesheetHistory.TimeSheetSource," _
& "TimesheetHistory.Type,TimesheetHistory.Reason,TimesheetHistory.Notes " _
& "FROM TimesheetHistory INNER JOIN CompanyData ON TimesheetHistory.LTRef = CompanyData.LTRef " _
& " ..."

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

 

[doctorswamp]

Great PHV, just the ticket, was going mad with all the red.

So normal code needs space+underscore {return} to split line, but code in quotes needs "space+underscore {return} at the split, then & " to start the next line.