Number of rows differ between macro exported data and using saved export

[rhi239]

I've set up a macro to export the whole of a query in Access 2013 to Excel 2013 however when the macro runs it misses some lines of data. If I export the data using saved exports then all the data is exported and no lines are missed. I've had a quick look at the missing lines of data and the macro seems to miss any data which has been added since the macro was last ran.

Can anyone help me with this please?

[ranman256]

This makes no sense. If the macro is using the docmd.Transferspreadsheet .... on the same query it should export ALL records.
Are you using the Transferspreadsheet command?

[rhi239]

At the moment I'm just using the access built in macro's as I'm not sure how to use the Transferspreadsheet command

[alansidman]

Is there criteria in the query limiting the date of records? Perhaps you should post the SQL statement for the Query and the code for the Macro. Otherwise any analysis will be akin to tossing darts while blindfolded.

[rhi239]

No problem the SQL statement is (the query is based on quite a few other queries):

SELECT [Activities 3_dates].activityID, [Activities 3_dates].caseID, [Activities 3_dates].Client, [Activities 3_dates].[Type of Activity], [Activities 3_dates].Case, [Activities 3_dates].[Activity Status], [Activities 3_dates].[Case Status], [Activities 3_dates].[Current Case Classification], [Activities 3_dates].contact, [Activities 3_dates].duration, [Activities 3_dates].[Clinical Specialist], [Activities 3_dates].natureofcall, [Activities 3_dates].calloutcome, [Activities 3_dates].natureofWork, [Activities 3_dates].trainingOrChargeable, [Activities 3_dates].[Created by], [Activities 3_dates].[Case Manager], [Activities 3_dates].[Complex Case Manager], [Activities 3_dates].methodOfCommunication, [Activities 3_dates].locationName, [Activities 3_dates].[Hours Completed], [Activities 3_dates].[Assessments Completed], [Activities 3_dates].attendees, [Activities 3_dates].[Date of Activity], [Activities 3_dates].[Case Manager in Required Attendee Format], [Activities 3_dates].[Attendee 1], [Activities 3_dates].[Attendee 2], [Activities 3_dates].[Attendee 3], [Activities 3_dates].[Attendee 4], [Activities 3_dates].[Attendee 5], [Activities 3_dates].[Attendee 6], [Activities 3_dates].[Attendee 7], [Activities 3_dates].[Attendee 8], [Activities 3_dates].[Attendee 9], [Activities 3_dates].[Is CM attendee], [Activities 3_dates].A1, [Activities 3_dates].A2, [Activities 3_dates].A3, [Activities 3_dates].A4, [Activities 3_dates].A5, [Activities 3_dates].A6, [Activities 3_dates].A7, [Activities 3_dates].A8, [Activities 3_dates].A9, [Activities 3_dates].[Day field], [Activities 3_dates].[Week field], [Activities 3_dates].[Month field], [Activities 3_dates].[Quarter field], [Activities 3_dates].[Year field], [Activities 3_dates].[Date of Activity Y&M], Lookup_Duration.Time, IIf([Attendee 1]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 2]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 3]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 4]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 5]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 6]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 7]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 8]=[Case Manager in Required Attendee Format],"Case Manager",IIf([Attendee 9]=[Case Manager in Required Attendee Format],"Case Manager","Someone else"))))))))) AS [Case Manager/Someone else], IIf([Case Manager]=[Created by],"Yes - Completed by Case Manager","No - Completed by Someone else") AS [Did the Case Manager created the activity], [Activities 3_dates].[Case Type]
FROM [Activities 3_dates] LEFT JOIN Lookup_Duration ON [Activities 3_dates].duration = Lookup_Duration.duration;

and the macro code:

Function Macro2()
On Error GoTo Macro2_Err
DoCmd.OpenQuery "Activities 4_duration", acViewNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Activities 4_duration", "ExcelWorkbook(*.xlsx)", "F:\MI Data\Automated MI Reports\Main Reporting Tool\AutomaticExportActivities2", True, "", , acExportQualityPrint

Macro2_Exit:
Exit Function
Macro2_Err:
MsgBox Error$
Resume Macro2_Exit
End Function

Thank you!

[alansidman]

Look at this attached file. It is a demo of how I export files to excel using the docmd.transfer spreadsheet. Follow this example and you should have no issues.

[qa12dx]

you r doing a join in ur qry. it will limit data per rule. table will show all data. use a query where there is no join, or maybe u didn't need thoses recs in the first place, that's why you left joined...