Query returns 20 records from QBE, but 13 when run thru DAO objects via code

[Pack10]

Got a wierd one here. I have a query which returns 20 records when run from the QBE.
I actually run the query thru VBA code and pass the recordset to excel. When I run the query thru
the code module, I get 13 records copied from the query in Access that make it into Excel.
Any ideas on what could be happening here.???????

 

[dhookom]

No idea without you posting code, SQL, example data, etc.

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Here is the code: When I test the recordset value (rs) down in the copyfromRecordset, it correctly shows 20 in the
rs.RecordCount property, so I dont understand why only 13 records end up in the worksheet tab.....


Function OutPut_2nd_Fail_Date()

On Error Resume Next

Dim fld As DAO.Field
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim x As Integer
Dim dte_Parameter As Date

Set db = CurrentDb
Set qdf = db.QueryDefs("2nd Fail Date")
dte_Parameter = DateValue(Forms!frmImport!dt_Meishan)
qdf.Parameters(0) = dte_Parameter
'qdf.Parameters(0) = #6/28/2013#
Set rs = qdf.OpenRecordset

DoCmd.SetWarnings False

Set fld = rs.Fields(0)

' This code fires the Excel from Access.

Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim xlRng As Object
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True

Set objXLBook = objXLApp.Workbooks.Open("S:\Quality Metrics\Input Files\Weekly SP Input Files\Query_Results.xlsx")
Set objXLSheet = objXLBook.Sheets("2nd Fail Date")
objXLSheet.Activate

objXLSheet.Range("A1C2000").ClearContents

' print column headers
x = 1
For Each fld In rs.Fields
objXLSheet.Cells(1, x).Value = fld.Name
x = x + 1
Next fld

Set xlRng = objXLSheet.Cells(2, 1)
xlRng.CopyFromRecordset rs
objXLSheet.Columns.AutoFit

' clean up
objXLApp.Application.Visible = False
objXLBook.Save
objXLBook.Close (True)

Set objXLApp = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set xlRng = Nothing

'objXLSheet.Range("A2C2000").ClearContents
'objXLSheet.Range("A2").CopyFromRecordset rs


' clean up
objXLApp.Application.Visible = False
objXLBook.Save
objXLBook.Close (True)

Set objXLApp = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set xlRng = Nothing

End Function

 

[dhookom]

I've not seen or heard of an issue like this before. I would try loop through the records and fields to write to the cells row by row.

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Yup good idea. Thx