Hi,
I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data.
Specifically, my situation is that I have a query that contains results from 2 tables that have a one to many relationship to each other (ex. 1=Contact, M=Contact Incident, 1 Contact can have multiple Contact Incidents). This query is the dataset for my report.
I want to be able to sort by fields from both “Contact” and “Contact Incident.” I have the following code on the “On Open” event of my report:
Me.GroupLevel(0).ControlSource = “<Contact field name goes here>”
Me.GroupLevel(1).ControlSource = “<Contact Incident field name goes here>”
In the report, I have two default group headers, “Groupheader1” (corresponding to Contact) and “Groupheader2” (corresponding to Contact Incident) which are preset to sort by Contact primary key and Contact Incident primary key.
The user decides which fields to sort by on a secondary selection form.
My problem is that when I open the report, I am missing records from the report. I have noticed two things: 1) if the record has a null entry in a field that I sorted by (ex. CONTACT_NAME, 80% of the time it will not show up 2) occasionally, the record belonging to “Contact” will not appear on the report, but the “Contact Incident” fields will appear but they will be shown belonging to another “Contact.”
This is kind of a bizarre error. I tried looking at the query itself, and all the records are shown. If I do not run any sorting or grouping on the report, all the records are displayed. I tried putting a textbox databound to “Count=(*)” on my report however the record count does not go down, even when I am missing records.
I have done some research and from what I read it seems that access is “combining” two of my rows and subsequently “hiding” the data.
Any ideas? Thanks in advance.