Information Displaying Incorrectly (1 Viewer)

[rekabeilla]

Hello,

(Please view the screenshots I have attached of the design view, print preview, table and record source to help narrow things down.)

I am currently designing a report that has a few sub-reports and I am having trouble with the display of one of these sub-reports. I want the sub-report to display past information, I have input expressions as the control source to these text boxes so the fields can shrink if null and to display information from multiple fields in one textbox. The sub-report's record source is set as a one to many relationship where one location can have many past clinics. In the screenshot, LocationID 65 has past clinic information from 2017 & 2018. I cannot figure out why the records are displaying on different "reports". I want it to display exactly how it is shown in design view. Any idea what I am doing wrong? Thank you!

 

[MajP]

In Sorting and Grouping are you grouping on year?

 

[plog]

Why do you have 2/4 sections? What is the full expression of all those sections?

 

[rekabeilla]

In Sorting and Grouping are you grouping on year?

There is currently nothing grouped or sorted, I tried to group by LocationID but that didnt work.

 

[rekabeilla]

Why do you have 2/4 sections? What is the full expression of all those sections?

I'm sorry, I'm not sure what you are asking

 

[plog]

In design view you have either 2 or 4 sections that the data can appear in. Why and what are the full expressions of them? I see a bunch of " IsNull()" statements, what's the full code of those?

 

[rekabeilla]

The 1st & 3rd textboxes are the "labels" so they can shrink when they are blank

=IIf([ClinicYear]="2017" And (Not IsNull([Flu]) Or Not IsNull([Bio] Or Not IsNull([Vitamins]))),"<b>2017:</b>","")

The 2nd & 4th are for the data (this is textbox 2's source)

=IIf(([ClinicYear]="2017" And Not IsNull([Flu]) And IsNull([Bio]) And IsNull([Vitamins])),"(Flu " & [Flu] & ")",IIf(([ClinicYear]="2017" And Not IsNull([Flu]) And Not IsNull([Bio]) And IsNull([Vitamins])),"(Flu " & [Flu] & ")  (Bio " & [Bio] & ")",IIf(([ClinicYear]="2017" And Not IsNull([Flu]) And Not IsNull([Bio]) And Not IsNull([Vitamins])),"(Flu " & [Flu] & ")  (Bio " & [Bio] & ")  (Vit " & [Vitamins] & ")",IIf(([ClinicYear]="2017" And Not IsNull([Flu]) And IsNull([Bio]) And Not IsNull([Vitamins])),"(Flu " & [Flu] & ")  (Vit " & [Vitamins] & ")",IIf(([ClinicYear]="2017" And IsNull([Flu]) And Not IsNull([Bio]) And IsNull([Vitamins])),"(Bio " & [Bio] & ")",IIf(([ClinicYear]="2017" And IsNull([Flu]) And Not IsNull([Bio]) And Not IsNull([Vitamins])),"(Bio " & [Bio] & ")  (Vit " & [Vitamins] & ")",IIf(([ClinicYear]="2017" And IsNull([Flu]) And IsNull([Bio]) And Not IsNull([Vitamins])),"(Vit " & [Vitamins] & ")","")))))))

I only got as far as 2017 & 2018, then i realized it wasn't displaying the way i was envisioning.

 

[plog]

Honestly it seems your using coding to overcome a basic understanding of reports in Access. I suggest you go through 2 tutorials:

Introduction to reports in Access - Microsoft Support

Use a report to print and share data from Access. This is an overview of Access reports where you’ll learn the basics of creating a report and using options like sorting, grouping, and summarizing the data, and how to preview and print the report.

support.microsoft.com

Guide to designing reports - Microsoft Support

support.microsoft.com

1. The year shouldn't be part of the expression, instead you should look at groupings. The first link will help with that.
2. I think a vertical report would eliminate the need for the expression at all. The second link will help with that.

Since this is just 1 subreport of a report with multiple ones, I'm kind of concerned about the entire layout of the rest of it. I would go through the tutorials I posted and then apply what they teach to every aspect of your report.

 

[rekabeilla]

Honestly it seems your using coding to overcome a basic understanding of reports in Access. I suggest you go through 2 tutorials:

Introduction to reports in Access - Microsoft Support

Use a report to print and share data from Access. This is an overview of Access reports where you’ll learn the basics of creating a report and using options like sorting, grouping, and summarizing the data, and how to preview and print the report.

support.microsoft.com

Guide to designing reports - Microsoft Support

support.microsoft.com

1. The year shouldn't be part of the expression, instead you should look at groupings. The first link will help with that.
2. I think a vertical report would eliminate the need for the expression at all. The second link will help with that.

Since this is just 1 subreport of a report with multiple ones, I'm kind of concerned about the entire layout of the rest of it. I would go through the tutorials I posted and then apply what they teach to every aspect of your report.

Thank you, I will look at these now. I appreciate the help!