Empty date field error

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

I would try remove the $ and change one comma to a period:

=IIf([HasData], Format([DistributionDate],"mmmm yyyy",0.00,"No Data")

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Hi Duanne

Problem solved. I had to do a little more digging and modify what you
suggested. So here it is.

First off I have two subreports. This was one of them. One counts families
one counts individuals for each month. The one I was working with continued
the #error with all of your suggestions. So I tried it on the other report. I
use these two statements in each corresponding
report.=IIf([DistributionDate]#1/1/2000#,Format$([DistributionDate],"mmmmyyyy",0,0),"No Clients")

=IIf([DistributionDate]#1/1/2000#,Format$([DistributionDate],"mmmm
yyyy",0,0),"No Families")

One worked and the other continued the #error.

Then I noticed that the Format$([DistributionDate]....... statement was in
the detail header on the report that worked and the report that didn't work
had it in the detail footer. (Not sure how I did that when I created one from
the other.) I switched the footer statement to the header and they both work.
I guess precedence goes from header to detail section to footer.

Anyway problem solved.

Thanks

Evan


"Duane Hookom" wrote:

I would try remove the $ and change one comma to a period:

=IIf([HasData], Format([DistributionDate],"mmmm yyyy",0.00,"No Data")

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Hi Duane,

I didn't bother to look at the actual print preview version of the report.
The two subreports open correctly in print preview. When I open the master
report they do not show up on the master report. What am I missing?

Thanks

Evan

"Duane Hookom" wrote:

I would try remove the $ and change one comma to a period:

=IIf([HasData], Format([DistributionDate],"mmmm yyyy",0.00,"No Data")

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

I don't know what you might be missing. What have you checked? Did you look
at the Link Master/Child? How else are the subreports filtered?

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duane,

I didn't bother to look at the actual print preview version of the report.
The two subreports open correctly in print preview. When I open the master
report they do not show up on the master report. What am I missing?

Thanks

Evan

"Duane Hookom" wrote:

I would try remove the $ and change one comma to a period:

=IIf([HasData], Format([DistributionDate],"mmmm yyyy",0.00,"No Data")

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan

Hi Duane,

The subreports are not linked. They "print previewed" correctly before the
changes with no records. (Just the #error in the one report.) Now with the
change they "print preview" correctly by themselves, but not as subreports in
the master report. I have not changed anything in the master report. I only
made the previous change to the "DistributionDate" text box in both and move
the text box from the detail footer to the detail header.

I will keep looking and let you know.

Thanks

Evan

"Duane Hookom" wrote:

I don't know what you might be missing. What have you checked? Did you look
at the Link Master/Child? How else are the subreports filtered?

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duane,

I didn't bother to look at the actual print preview version of the report.
The two subreports open correctly in print preview. When I open the master
report they do not show up on the master report. What am I missing?

Thanks

Evan

"Duane Hookom" wrote:

I would try remove the $ and change one comma to a period:

=IIf([HasData], Format([DistributionDate],"mmmm yyyy",0.00,"No Data")

--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Hi Duanne,

I am opening only the subreport that has DistributionDate in it. The text
box is in the group footer. I copy and pasted your recommended statement into
the cotrol source. Name Autocorrect features are turned off.

This time when I went to save the report I got this error " The expression
you entered has a function containing the wrong number of arugments." When I
click OK it reverts back to the original.

Thanks,

Evan

"Duane Hookom" wrote:

Yes, this is a control source. I don't know what would cause it to revert
back to the original. Did you open the report/subreport in design view? Do
you have all Name Autocorrect options turned off?

It isn't clear whether the text box is in the report or subreport.
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

Where do I put this? In the Control Source for DistributionDate? If so I
tried that and it did not work. The Control Source reverted back to the
original.

Evan

"Duane Hookom" wrote:

You may need to use the HasData property of the report:
=IIf([HasData], Format$([DistributionDate],"mmmm yyyy",0,00,"No Data")
--
Duane Hookom
Microsoft Access MVP


"A deer in the digital media headlights!!" wrote:

I have a report that displays the month, year and a headcount sum for that
month and year. The problem I am having is that if I run the report before I
have matching data I get #Error in place of the month and year. I would like
to display "No Data" and 0 (zero) in the report if there is no data for the
month.

The report pulls from a select query. The field in question is
DistributionDate. The report formats DistributionDate to show "January 2009"
using the following.

From the Property Sheet Control Source =Format$([DistributionDate],"mmmm
yyyy",0,0).

All of this is happening in the group footer of the report.

This report is a subreport for another report so I need for the information
to show "No Data" instead of displaying nothing or the #Error.

Thanks in advance,

Evan