Problem with data not being displayed

[anubis7000]

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=Pet, M=Shots, 1 pet can have multiple shots). This query is the dataset for my report.

I want to be able sort by fields from both “Pet” and “Shots”, so I am using the following code:

Me.GroupLevel(0).ControlSource = “<Pet field name goes here>”
Me.GroupLevel(1).ControlSource = “<Shot field name goes here>”

In the report, I have two default group headers, “Groupheader1” (corresponding to Pet) and “Groupheader2” (corresponding to shots) which are preset to sort by Pet primary key and Shot 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. PET_NAME, 80% of the time it will not show up 2) occasionally, the record belonging to “Pet” will not appear on the report, but the “Shot” fields will appear but they will be shown belonging to another “Pet.”

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 exporting my tables/forms/queries/reports to another mdb file. All attempts have been unsuccessful.

I tried using the “OrderBy” and “OrderByOn” properties, but they do not seem to even work as when I use them the report remains unsorted.

Any suggestions? Thanks in advance.

 

[dhookom]

Use the GroupLevel Control Sources rather than the Order By.

What is you exact code? The following looks like pseudo code:

Me.GroupLevel(0).ControlSource = “<Pet field name goes here>”
Me.GroupLevel(1).ControlSource = “<Shot field name goes here>”

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[anubis7000]

The code for the report is below. Before continuing, it should be known that my report does not have "PET" and "SHOT" tables, as I was trying to be as simple as possible.

In my report, the data actually comes from two tables: CONTACT which contains contact information about people and CONTACT_INCIDENT (CI) which lists information about each contact incident for that contact. CONTACT has a one-to-many relationship with CONTACT_INCIDENT. So CONTACT = PET and CONTACT_INCIDENT = SHOTS

The sort order is determined by a field on a secondary form (Forms!contacts2!SORT). So for example, if SORT equals CONTACT_TITLE, then the report, or at least all the CONTACT information is supposed to be ordered by CONTACT_TITLE.

Here is the code:

'========================================

Private Sub Report_Open(Cancel As Integer)

'Reports![CONTACT_RPT].OrderBy = "CONTACT_MNAME"
'Reports![CONTACT_RPT].OrderByOn = True


'sort form (1:M = 1 data)

If Forms!contacts2!SORT = "CONTACT_TITLE" Then
Me.GroupLevel(0).ControlSource = "CONTACT_TITLE"
End If

If Forms!contacts2!SORT = "CONTACT_LNAME" Then
Me.GroupLevel(0).ControlSource = "CONTACT_LNAME"
End If

If Forms!contacts2!SORT = "CONTACT_FNAME" Then
Me.GroupLevel(0).ControlSource = "CONTACT_FNAME"
End If

If Forms!contacts2!SORT = "CONTACT_MNAME" Then
Me.GroupLevel(0).ControlSource = "CONTACT_MNAME"
End If

If Forms!contacts2!SORT = "ADDRESS_BIZ_STREET" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_BIZ_STREET"
End If

If Forms!contacts2!SORT = "ADDRESS_BIZ_CITY" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_BIZ_CITY"
End If

If Forms!contacts2!SORT = "ADDRESS_BIZ_STATE" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_BIZ_STATE"
End If

If Forms!contacts2!SORT = "ADDRESS_BIZ_ZIP" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_BIZ_ZIP"
End If

If Forms!contacts2!SORT = "ADDRESS_BIZ_ZIP" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_BIZ_ZIP"
End If

If Forms!contacts2!SORT = "ADDRESS_HOME_STREEET" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_HOME_STREEET"
End If

If Forms!contacts2!SORT = "ADDRESS_HOME_CITY" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_HOME_CITY"
End If

If Forms!contacts2!SORT = "ADDRESS_HOME_STATE" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_HOME_STATE"
End If

If Forms!contacts2!SORT = "ADDRESS_HOME_ZIP" Then
Me.GroupLevel(0).ControlSource = "ADDRESS_HOME_ZIP"
End If

If Forms!contacts2!SORT = "EMAIL" Then
Me.GroupLevel(0).ControlSource = "EMAIL"
End If

If Forms!contacts2!SORT = "LAST_EVENT_ATTENDED" Then
Me.GroupLevel(0).ControlSource = "LAST_EVENT_ATTENDED"
End If


'sort subform (1:M = M data)
If Forms!contacts2!SUBSORT = "CI_DATE" Then
Me.GroupLevel(1).ControlSource = "CI_DATE"
End If

If Forms!contacts2!SUBSORT = "CI_KEYWORD" Then
Me.GroupLevel(1).ControlSource = "CI_KEYWORD"
End If

If Forms!contacts2!SUBSORT = "CI_NOTE" Then
Me.GroupLevel(1).ControlSource = "CI_NOTE"
End If

If Forms!contacts2!SUBSORT = "CI_X" Then
Me.GroupLevel(1).ControlSource = "CI_X"
End If
End Sub

'===============================================

Hope this helps. Thnx

 

[dhookom]

I'm not sure why you don't get rid of a ton of code and use something like:

Me.GroupLevel(0).ControlSource = Forms!contacts2!SORT 
If Not IsNull(Forms!contacts2!SUBSORT) Then
  Me.GroupLevel(1).ControlSource = Forms!contacts2!SUBSORT
End If

Try place a text box in the report header section with a control source of:
=Count(*)
See if this matches the expected number of records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Guiderman]

Check the data.

I've seen this problem before with sorting and grouping data, Access may combine two rows of data into one, hiding your data. (if that makes any sense.)

 

[maxhugen]

when the report is run using sorting/grouping I am missing data

Check that the report's query hasn't been set to DISTINCT - ie check Query Properties that both Unique Values and Unique Records are both set to No.

If that's not the cause, run the query itself to check that you are in fact getting the correct number of records returned. Do you have one or many tables in your query?

Max Hugen
Australia

 

[anubis7000]

Hi everyone,

Sorry for the late reply. I got reassigned to another project, and this is why it has taken me so long to get back.

I am still having the same problem. I looked at the underlying query, and all the data (a total of 15 rows) are there.

I tried putting a text box whose control source is “count=(*).” Even when I am missing data, it displays the total number of records as 15.

As suggested by Guiderman, it seems that access is combining/hiding my data, although I am stumped as to how to fix it.

Thanks.

 

[dhookom]

If you suggest there are 15 records that should appear in the report and the Count(*) text box shows 15 then I expect you are putting control in a group section rathert than the detail section.

If you group by Pet Name, won't you have duplicate names?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rxsims]

Guiderman - how do you "uncombine" these? I am having the same problem in a report:

I have two fields in Table1(ReasonID (primary key); and ReasonDescription), and data like this:

ReasonID ReasonDescription
1 this
2 that
3 the Other

This data is used in Table2, where the Field is:
ReasonDescription
There is no reference to the ReasonID that corresponds in Table2.

In my report, when I do not group or sort, the ReasonDescription shows (as desired). When I do group, the ReasonID is shown (not as desired).

The control is the exact same for both!

Any help is greatly appreciated!

 

[dhookom]

It sounds like you are using lookup fields. If so, consider reading about the evils at

http://www.mvps.org/access/lookupfields.htm.

Look you your table designs to see if the fields you think are text are actually numeric and are lookup fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]