This is a re-post of an unresolved problem. I hope this is a better explanation.
I am so stuck!
I have developed an Access database for a colleaque to store scientific sample data. My contract finishes in a week and the database is done, except for a critical part of the project. My problem is that the key report for outputting the data does not export well to .xls format. Duane Hookam (Microsoft MVP) has given me great pointers on how to fix it by using Group Headers effectively, but I could not get it to work. I think it is a tricky report to get right and I am not that knowledgable on how to manipulate Access Reports.
The goal is to hide duplicates intelligently using Group Headers, for 8 levels of grouping. I have produced one report that looks right, but exports wrongly, described below. Then I duplicated it and came pretty close with Duane's method, but cannot get it right. Then I triplicated it for you to play with to see if you can make it work. There are 8 Grouping levels. If you can just show me with 3 levels, I believe I could then extend your method to the other levels.
I have attached a stripped down database that contains just the database objects needed to produce my report.
There are 3 versions of it...
+1) rptInfo1A:
This version works great as an Access Report. It looks the way I want it.---It just doesn't export to Excel correctly. The grouping and hiding of duplicates is done with in VBA code. I believe that is the source of the poor exportability---When exporting, columns 2 to 13 are missing; [Year] and [Point] are there, but all fields between them do not export. You can try that by exporting rptInfo1A.
+2) rptInfo1AClose:
This version is my best result (from stumbling in the dark). It looks wild since all the headers have colour-coded backgrounds (so I could see what was happening). Ignore the colours and look at the columns of data. By comparing it to rptInfo1a, you can see that the grouping and hiding of duplicates half works. So I think I am 'Close', but after trying every combination of moving "MoveLayout = False" and reading the textboxes with "ControlSource" = 1, I have run out of ideas. I really don't understand how Access processes reports.
+3) rptInfo1AHeaders:
This testing version, for you to play with, has all the headers for sorting and grouping, and there is no coding at all. Hide Duplicates is set to no for all fields. If I set it to Yes for all appropriate fields, some values that should be shown (being the first instance of that particular value in that field for one of the groupings) are hidden, in spite of the fact that the previous record has the same value in that field.
Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
I am so stuck!
I have developed an Access database for a colleaque to store scientific sample data. My contract finishes in a week and the database is done, except for a critical part of the project. My problem is that the key report for outputting the data does not export well to .xls format. Duane Hookam (Microsoft MVP) has given me great pointers on how to fix it by using Group Headers effectively, but I could not get it to work. I think it is a tricky report to get right and I am not that knowledgable on how to manipulate Access Reports.
The goal is to hide duplicates intelligently using Group Headers, for 8 levels of grouping. I have produced one report that looks right, but exports wrongly, described below. Then I duplicated it and came pretty close with Duane's method, but cannot get it right. Then I triplicated it for you to play with to see if you can make it work. There are 8 Grouping levels. If you can just show me with 3 levels, I believe I could then extend your method to the other levels.
I have attached a stripped down database that contains just the database objects needed to produce my report.
There are 3 versions of it...
+1) rptInfo1A:
This version works great as an Access Report. It looks the way I want it.---It just doesn't export to Excel correctly. The grouping and hiding of duplicates is done with in VBA code. I believe that is the source of the poor exportability---When exporting, columns 2 to 13 are missing; [Year] and [Point] are there, but all fields between them do not export. You can try that by exporting rptInfo1A.
+2) rptInfo1AClose:
This version is my best result (from stumbling in the dark). It looks wild since all the headers have colour-coded backgrounds (so I could see what was happening). Ignore the colours and look at the columns of data. By comparing it to rptInfo1a, you can see that the grouping and hiding of duplicates half works. So I think I am 'Close', but after trying every combination of moving "MoveLayout = False" and reading the textboxes with "ControlSource" = 1, I have run out of ideas. I really don't understand how Access processes reports.
+3) rptInfo1AHeaders:
This testing version, for you to play with, has all the headers for sorting and grouping, and there is no coding at all. Hide Duplicates is set to no for all fields. If I set it to Yes for all appropriate fields, some values that should be shown (being the first instance of that particular value in that field for one of the groupings) are hidden, in spite of the fact that the previous record has the same value in that field.
Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
