Repeated rows in excel (exported report)

[BigJohn89]

Hi,

I have report which have to be exported to an excel file.
Results are displaing correctly, print and pdf file is also correct, but when I create an excel file - then starting from certain row - data is repeated until the end of the report.
I've tried macros and vba - results are the same.

Has anyone encountered such a problem?

[Perceptus]

Screenshot please thanks

[Micron]

I have not but have read that certain groups, such as on calculated controls can cause this. Suggest you backup the report and remove such groups or any fields that toggle and see what happens. A pic of your report design view might help but this one may require a copy of the db.

[BigJohn89]

Hello, I use aggregate query to sum time (czas) from few different forms. In report I paste order numbers (zlecenie) to 50 textbox. Then I create report from these texboxes and export excel file.

Aggregate Query:

SELECT zlecenie, user_ID, time_cord AS time_enter, date_cord AS date_enter, quantity, "Cord" AS Category FROM tblCord
UNION SELECT zlecenie, user_ID, time_fabric, date_fabric, quantity, "Fabric" FROM tblFabric
UNION SELECT zlecenie, user_ID, time_montage, date_montage, quantity, "Montage" FROM tblMontage
UNION SELECT zlecenie, user_ID, time_packing, date_packing, quantity, "Packing" FROM tblPacking
UNION SELECT zlecenie, user_ID, time_holes, date_holes, quantity, "Holes" FROM tblHoles
UNION SELECT zlecenie, user_ID, time_profile, date_profile, quantity, "Profile" FROM tblProfile;


Then I use aggregate query in second query (it's a source of report)

SELECT aggQuery.zlecenie, aggQuery.time_enter, Val(Left([aggQuery]![time_enter],2)) AS Hrs, Val(Mid([aggQuery]![time_enter],4,2)) AS Mins, Val(Mid([aggQuery]![time_enter],7,2)) AS Secs, ([Hrs]/60+[Mins]+[Secs]*0.016)/60 AS TotalSecs
FROM aggQuery
WHERE (((aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst1] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst2] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst3] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst4] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst5] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst6] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst7] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst8] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst9] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst10] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst11] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst12] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst13] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst14] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst15] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst16] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst17] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst18] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst19] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst20] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst21] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst22] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst23] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst24] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst25] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst26] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst27]) AND ((aggQuery.time_enter)>"00:00:00") AND ((Len([zlecenie] & ""))>1)) OR (((aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst28] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst29] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst30] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst31] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst32] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst33] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst34] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst35] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst36] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst37] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst38] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst39] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst40] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst41] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst41] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst42] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst43] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst44] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst45] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst46] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst47] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst48] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst49] Or (aggQuery.zlecenie)=[Forms]![frmTimeReports]![Tekst50]));



Report:



Report design view:


Exported excel file: (after 3rd row value in time (czas) is repeating:

[Micron]

If you view the query output as a datasheet, do the time calculations look the same as what's going into Excel? As previously stated, calculated fields can cause this, especially (as far as I know) if the result of even just one iteration of the calculation results in a Null. Your solution might be to create a working query and use TransferSpreadsheet method to export to Excel.
**You have not said what method you are using to enter this data into Excel.

[BigJohn89]

I've used Macro, TransferSpreadsheet method and OutputTo method - results are the same
Report is based on a query but I've used grouping and sorting (necessary condition). I even tried to export only query to excel - but this is not what I need.


EDIT: I noticed that during exporting report to excel (by clicking right mouse button on report-->export to excel) data in spreadsheet is correct

[Micron]

So you are saying exporting query to Excel gives incorrect results, but exporting the report to Excel is correct, yet the query for the report is the exact same query as the query that doesn't properly export? Then I have no idea as to why.

[BigJohn89]

I use Query to split time from format 00:00:00 to hours, minutes and seconds with calculation (TotalSecs TotalSecs: ([Hrs]/60+[Mins]+[Secs]*0,016)/60). Thats why results in query are incorrect. In report I use summing and grouping and then I Have result I need.

Result in Query:


Result in report:


Is there a way to do such grouping and summing in query? Then I could make excel file directly from query.

[Micron]

You can sum and group by using a Totals query. No idea if that will solve your problem though.
Don't understand how 11 minutes and 42 seconds equates to what you show. I would have thought the answer to be 702 seconds.

[BigJohn89]

Time is calculating to SAP time format. Every production order has its own production time - "working hours".
[11 min + 42 sec*0,016)/60 = 0,19453 HRS

[BigJohn89]

So I think I almost got it. I've created new query by using source query for report. Then instead of exporting report to an excel - I'm exporting query.
Rows are now correct but even when I applied rounding in options to 3 places - in excel I still have rounding to 2 places.
Results in query are correct.





It's there a way to do such rounding via VBA?

[Micron]

Not sure if you kind of fell into that solution by yourself, or if you were following my suggestion from post 5. I don't do much query exporting, which can be done at least one of 2 ways IIRC, and I don't think either of them can control formats. However, one way is to use an Excel template, the idea being to export/transfer to a properly formatted worksheet. The only other way I know of that might work is Automation (controlling Excel from Access). You could try formatting in the query field itself if you haven't already