CopyFromRecordset to Excel Problem

[toddmoses26]

I've got code that builds a DAO recordset from a table, and then pastes
the recorset in a specified area in an Excel worksheet.

************************************************** *
Function AccessToExcelAutomationEP()

Dim db As Database
Dim rsCMRSummary As DAO.Recordset
Dim wbkNew As Excel.Workbook
Dim wksNew As Excel.Worksheet
Dim rngCurrSummary As Excel.Range

Set db = CurrentDb
'Opens the recordset
Set rsCMRSummary = db.OpenRecordset("Select Descr, TotalEmployees,
MinMales, ([MinMales]/[TotalEmployees]) as '%MaleMin' from
tblEPFinal_MinMaleFemale;")

'Opens a new Excel workbook, and creates a new worksheet for the report
Set appExcel = New Excel.Application
Set wbkNew = appExcel.Workbooks.Add
Set wksNew = wbkNew.Worksheets.Add
appExcel.Visible = True

'Selects the Excel range, and then pastes the recordset into that range
Set rngCurrSummary = wksNew.Range("H21:K32")
rngCurrSummary.CopyFromRecordset rsCMRSummary

End Function
************************************************

The code works as intended, however, if I have a row in the table that
all the values are "0", the recorset holds all the rows, but does not
paste from the row with all 0's to the end of the recordset.

i.e.

Professionals 10 10 10
Sales 10 10 10
Technical 0 0 0
Labor 99 99 99

will only print the first two rows.

But if I have:

Professionals 10 10 10
Sales 10 10 10
Technical 15 15 15
Labor 99 99 99

Then all four rows paste to Excel.

I would appreciate any help that you could provide.

[kenfirch]

Maybe it's a divide by zero / #Error issue. Try "Select Descr,
TotalEmployees,
MinMales, iif([TotalEmployees]=0,Null,[MinMales]/[TotalEmployees]) as
'%MaleMin' from
tblEPFinal_MinMaleFemale;"

Ken

[toddmoses26]

Thank you for your reply.

I kept working on this. The problem was that I was creating the value
in "%MaleMin' when I opened the recordset, versus when I populated the
table in the first place.

I did have to include some conditions for if the numerator or
denominator were 0.