Report with disappearing subreports - null values

[Mary]

I have an unbound report containing two subreports of annual information. If
we fix one then I can apply the same method to the other.

I need the subreport to show data, with the following as an example:

BIRTHDAYS
Thursday April 1, 2008:
Mr. Bentley
Mrs. Smith
Mr. Zanders

Friday April 2, 2008:
Ms. Pennyloafer

Happy Birthday!


The report looks very good, the only problem is that I cannot seem to get it
to return what I need when there is no birthday for the day or weekend. When
there are no birthdays the subreport doesn't show at all but I need it to
show this:

Friday April 2, 2008:
None


The following is the setup of the subreport:

Report Header
BIRTHDAYS
Page Header
Month Header
Day Header
=IIf([Month]="01","January","") & IIf([Month]="02","February","") &
IIf
([Month] ="03","March","") & IIf([Month]="04","April","") & IIf([Month]
="05","May","") & IIf([Month]="06","June","") &
IIf([Month]="07","July","") & IIf
([Month]="08","August","") & IIf([Month]="09","September","") &
IIf([Month]
="10","October","") & IIf([Month]="11","November","") & IIf([Month]
="12","December","") & " " & [Day]
Detail
=IIf([Index Name] Is Null,"None",[Index Name])
Page Footer
Report Footer
=IIf([Index Name] Is Null,"","Happy Birthday!")

At this point all is running quite well except when the query runs and there
are no birthdays the subreport doesn't appear at all. Oh, the name of the
day of the week (friday, etc) does not show but that is minor. I would just
like to tackle the if/then statement so the subreport returns the date and
"none" rather than disappearing.

Any suggestions?

 

[Duane Hookom]

There are no page sections in a subreport. I would create a table of all
dates tblDates with a single field datDate and all possible dates. Use this
as the record source of your main report and include a subreport joined by
month and day to your subreport in the detail section. Include a text box in
the main report like:
=IIf(sbrptBDDate.Report.HasData, Null, "None")

BTW: Your huge IIf() statement should be able to be replaced by:
=MonthName(Val([Month]))

 

[Mary]

Hi Duane, thank you for responding, I appreciate your time. Thank you also
for that nice little statement for the months, you just keep making me more
and more efficient (I have also benefited from your answers to other member
questions).

The page header and footer were inserted by the program and there is nothing
in them, so I can delete those, no problem.

I am trying to wrap my brain around your suggestion and need some help
thinking this through...
The good news is that I understand the mechanics of your suggestion. But I
am fuzzy about the maintenance and execution of the solution. One of the
other subreports is the dates of members who have died, so I have dates going
all the way back to the early 1900s. By "all possible dates" do you mean all
dates that someone was born, joined, or died, etc.? Would the easiest way to
do this be to create a new table and perform an append query to send the
dates into it from our "vitals" table? Would this be considered maintaining
redundant information? Is there some way to set the table up so it updates
when I update the vitals table? Or do I have to remember to run a query when
there is a new member, death, etc.?

I am willing to do what needs doing, if I can be confident of maintaining
it.

There are no page sections in a subreport. I would create a table of all
dates tblDates with a single field datDate and all possible dates. Use this
as the record source of your main report and include a subreport joined by
month and day to your subreport in the detail section. Include a text box in
the main report like:
=IIf(sbrptBDDate.Report.HasData, Null, "None")

BTW: Your huge IIf() statement should be able to be replaced by:
=MonthName(Val([Month]))

--
Duane Hookom
Microsoft Access MVP

I have an unbound report containing two subreports of annual information. If
we fix one then I can apply the same method to the other.

I need the subreport to show data, with the following as an example:

BIRTHDAYS
Thursday April 1, 2008:
Mr. Bentley
Mrs. Smith
Mr. Zanders

Friday April 2, 2008:
Ms. Pennyloafer

Happy Birthday!


The report looks very good, the only problem is that I cannot seem to get it
to return what I need when there is no birthday for the day or weekend. When
there are no birthdays the subreport doesn't show at all but I need it to
show this:

Friday April 2, 2008:
None


The following is the setup of the subreport:

Report Header
BIRTHDAYS
Page Header
Month Header
Day Header
=IIf([Month]="01","January","") & IIf([Month]="02","February","") &
IIf
([Month] ="03","March","") & IIf([Month]="04","April","") & IIf([Month]
="05","May","") & IIf([Month]="06","June","") &
IIf([Month]="07","July","") & IIf
([Month]="08","August","") & IIf([Month]="09","September","") &
IIf([Month]
="10","October","") & IIf([Month]="11","November","") & IIf([Month]
="12","December","") & " " & [Day]
Detail
=IIf([Index Name] Is Null,"None",[Index Name])
Page Footer
Report Footer
=IIf([Index Name] Is Null,"","Happy Birthday!")

At this point all is running quite well except when the query runs and there
are no birthdays the subreport doesn't appear at all. Oh, the name of the
day of the week (friday, etc) does not show but that is minor. I would just
like to tackle the if/then statement so the subreport returns the date and
"none" rather than disappearing.

Any suggestions?

 

[Duane Hookom]

Actually, you only need a table of numbers tblNums with a single numeric
field [Num] and 366 records (1 to 366). Create a query that can be the record
source of your main report like:
SELECT Format(#1/1/2008#+[Num]-1,"mmdd") AS MthDay
FROM tblNums
GROUP BY Format(#1/1/2008#+[Num]-1,"mmdd");

Create a similar column in your subreport and use it in the Link Master/Child.

If you can't figure this out, we need to know how you are storing birthdates.



--
Duane Hookom
Microsoft Access MVP

Hi Duane, thank you for responding, I appreciate your time. Thank you also
for that nice little statement for the months, you just keep making me more
and more efficient (I have also benefited from your answers to other member
questions).

The page header and footer were inserted by the program and there is nothing
in them, so I can delete those, no problem.

I am trying to wrap my brain around your suggestion and need some help
thinking this through...
The good news is that I understand the mechanics of your suggestion. But I
am fuzzy about the maintenance and execution of the solution. One of the
other subreports is the dates of members who have died, so I have dates going
all the way back to the early 1900s. By "all possible dates" do you mean all
dates that someone was born, joined, or died, etc.? Would the easiest way to
do this be to create a new table and perform an append query to send the
dates into it from our "vitals" table? Would this be considered maintaining
redundant information? Is there some way to set the table up so it updates
when I update the vitals table? Or do I have to remember to run a query when
there is a new member, death, etc.?

I am willing to do what needs doing, if I can be confident of maintaining
it.

There are no page sections in a subreport. I would create a table of all
dates tblDates with a single field datDate and all possible dates. Use this
as the record source of your main report and include a subreport joined by
month and day to your subreport in the detail section. Include a text box in
the main report like:
=IIf(sbrptBDDate.Report.HasData, Null, "None")

BTW: Your huge IIf() statement should be able to be replaced by:
=MonthName(Val([Month]))

--
Duane Hookom
Microsoft Access MVP

I have an unbound report containing two subreports of annual information. If
we fix one then I can apply the same method to the other.

I need the subreport to show data, with the following as an example:

BIRTHDAYS
Thursday April 1, 2008:
Mr. Bentley
Mrs. Smith
Mr. Zanders

Friday April 2, 2008:
Ms. Pennyloafer

Happy Birthday!


The report looks very good, the only problem is that I cannot seem to get it
to return what I need when there is no birthday for the day or weekend. When
there are no birthdays the subreport doesn't show at all but I need it to
show this:

Friday April 2, 2008:
None


The following is the setup of the subreport:

Report Header
BIRTHDAYS
Page Header
Month Header
Day Header
=IIf([Month]="01","January","") & IIf([Month]="02","February","") &
IIf
([Month] ="03","March","") & IIf([Month]="04","April","") & IIf([Month]
="05","May","") & IIf([Month]="06","June","") &
IIf([Month]="07","July","") & IIf
([Month]="08","August","") & IIf([Month]="09","September","") &
IIf([Month]
="10","October","") & IIf([Month]="11","November","") & IIf([Month]
="12","December","") & " " & [Day]
Detail
=IIf([Index Name] Is Null,"None",[Index Name])
Page Footer
Report Footer
=IIf([Index Name] Is Null,"","Happy Birthday!")

At this point all is running quite well except when the query runs and there
are no birthdays the subreport doesn't appear at all. Oh, the name of the
day of the week (friday, etc) does not show but that is minor. I would just
like to tackle the if/then statement so the subreport returns the date and
"none" rather than disappearing.

Any suggestions?