Groups in a Report Don't Work 3

[bethabernathy]

Hi, I am trying to generate a report with several levels of grouping. I can get the grouping to work, but I can't get the group headers to display properly. This is a database of skiing accidents. I want to set up groups by type of accident. I have two groups, "Lift Related" & "Ski School." I want the groups to be unique in the report. So all Lift Related records start the report and Ski School records come next. I don't know why I can't get this to work, it seems quite simple. At this point, the group headers are repeating within the groups. Any suggestions?

Thanks.

 

[Nancy2]

Is the grouping working correctly but the group headers just repeating?

 

[bethabernathy]

Hi Nancy: Yes, the grouping is working, it is just the headers that I can't get to work properly. Any suggestions?

Thanks, Beth

 

[Nancy2]

Either move the field containing the header to the group header section.

Or, Select the repeating field in the detail section and set the hide duplicates property to Yes.

 

[bethabernathy]

Hi Nancy: I put the field in the group section and it worked perfectly. Now I have another challenge, the fields controlling the groups are "Yes/No" fields. So, for example, I am grouping records related to skiing accidents, and the goal is to group the records in the report by the type of accident (i.e. "lift related" or "lesson related&quot. So, putting the field "lift related" which is a "Yes/No" field, then displays "Yes" as the title for the group. Is there a work around for this? Thank you so much for your help and information, I really appreciate it.

Beth

 

[Nancy2]

1) rename the control in the group header something like txtAccidentType (it can not be named the same as a field or else you'll get an error)

2) Set the control source of txtAccidentType to:

=iif([lift related] = -1, "Lift Related", "Lesson Related&quot

 

[bethabernathy]

That is awesome and works. I forgot one thing... The "Yes/No" field is not an actual "Yes/No" field, it is a combo box that I created for users to pull down either yes or no when entering data. I am finding that this may not be the way to go and maybe I should use the real "yes/no" fields that are available when you create your table. I tried using the syntax:

=iif([lift related] = Yes, "Lift Related&quot

and it doesn't like that. Is there any other way to do this using my fields or should I change the fields to the real "yes/no" field? If I do change the field, which I think is the way to go, is there a way to make the "Yes/No" field display as "Yes/No" rather than a check box which seems to be the default?

Thanks, I would be interested in finding a way to compensate you and the people who have helped me on this forum - is that possible?

Beth

 

[Nancy2]

Here you go -- this one also traps for null values since you are not using a yes/no

=iif([lift related] = "Yes", "Lift Related", iif([lift related] = "No", "Lesson Related", "Unknown&quot)

 

[tempclerk]

If your field is actually text, your Yes needs to be in quotes.

It seems to me you need to have not yes/no fields, but one text field in which you can enter "Lift Related", "Lesson Related", etc. Unless the incident can be Both lift and lesson related, in which case your grouping is going to get complicated anyway.

 

[bethabernathy]

Is there a way to create the syntax so if "liftrelated" is "Yes" then display "lift related" at the header without using the lesson portion of the syntax as that is it's own unique group?

Thanks Beth

 

[bethabernathy]

Hi Again:

This is working

=IIf([LiftRelated]="Yes","Lift Related&quotwhen placed in the header control, but the "Lift Related" isn't displaying in the header.

Beth

 

[Nancy2]

What I described before should work -- if you are grouping by [lift related] - the Nos will come first and the Yes will come in the second group.

If you like, Create a query for this report -- create a calculated field Named AccidentType and group by AccidentType on the report.

The field would appear in the query as:

AccidentType: iif([lift related] = "Yes", "Lift Related", iif([lift related] = "No", "Lesson Related", "Unknown&quot)

Then change the record source of the Report from the table to the query. Or you can just create a sql string from the record source which does the same.

 

[bethabernathy]

The only thing is that I don't want to display those records where "Lift Related" is "No." I have gotten the group display to work perfectly when I change the field to an actual "Yes/No" field, but don't understand why it won't display when the field is a combo box/look-up? I think the combo box needs to go and I need to use real "Yes/No" fields as my queries, etc. aren't really recognizing the data without some tricks. But, is there a way to get a "Yes/No" field to display "Yes" or "No" rather than the check box?

Thanks, Beth

 

[Nancy2]

True, if change the data type to Yes/No you will need to change all your queries.

If you decide to leave it Yes/No, you can fill your combo box with a value list of
-1,Yes;0,No
2 columns
bound column = 1
column widths: 0;.5

Then the user will see Yes or No in the pull down and and will fill the value of -1 or 0.

Your report probably should be based on a query which filters for just the NO's

 

[tempclerk]

> Is there a way to create the syntax so if "liftrelated" is "Yes" then display "lift related" at the header without using the lesson portion of the syntax as that is it's own unique group?

If you do this, then you'll have to do the exact same thing for every other group, just changing the words. The Point of a Group Header is that it prints automatically at the top of each group, and you only need to define it once.

The simple answer to your question is yes, use
=iif([lift related] = "Yes", "Lift Related","&quot, and then you'll need another control:
=iif([lesson related] = "Yes", "Lesson Related","&quot, and another control for each additional group.

 

[tempclerk]

> I don't want to display those records where "Lift Related" is "No."

What ARE you grouping on? If you don't want to display those records, you need to filter them out, and no grouping need be involved.

 

[bethabernathy]

The simple answer to your question is yes, use
=iif([lift related] = "Yes", "Lift Related","&quot, and then you'll need another control:
=iif([lesson related] = "Yes", "Lesson Related","&quot, and another control for each additional group.

This seems like a good option, but the group header still is not displaying "Lift Related" as the title for the grouping.

I am going to group on a number of possible accident types in the report (i.e. "Lift Related", "Ambulance Involved", "Ski School Related&quot etc. These are all fields in the table.

I have this sorting working with new fields that are actual "Yes/No" fields (i.e. check boxes) and the using the following syntax for the Lift Related Group:

=IIf([Newlift]=-1,"Lift Related&quot

and the following syntax for the Lesson related Group:

=IIf([Newlesson]=-1,"Ski School Related&quot

The headers display perfectly and the data is accurate within the detail. I didn't really totally understand how I can get it so that the real "Yes/No" field which displays as a check box can be displayed as Yes or No instead of the check box. Sorry if I am missing something. I really appreciate your help.

Beth

 

[bethabernathy]

Sorry I don't think I am making it all real clear. How do I get my new "Yes/No" check box fields to display as "Yes" or "No" on my forms? I have it working on my report. Thanks very much.

Beth

 

[CosmoKramer]

Try adding a text box with something like this as the control source:

=IIf([txtLiftRelated]=-1,"Yes","No")

 

[bethabernathy]

Hi -
I have several different options now, due to all of the great help I have gotten. Where am I to use this and what question does it relate to:

=IIf([txtLiftRelated]=-1,"Yes","No&quot

Thanks, Beth