Empty date field error

  • Thread starter Thread starter A deer in the digital media headlights!!
  • Start date Start date
A

A deer in the digital media headlights!!

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")
 
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 said:
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!! said:
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
Click to expand...
Click to expand...
 
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!! said:
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 said:
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!! said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!! said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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!! said:
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 said:
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!! said:
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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!! said:
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 said:
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


:

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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!! said:
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 said:
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


:

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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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!! said:
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 said:
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!! said:
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

:

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


:

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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!! said:
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 said:
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


:

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

:

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


:

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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
If a subreport doesn't return any records, it will not display at all in the
main report.
--
Duane Hookom
Microsoft Access MVP


A deer in the digital media headlights!! said:
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 said:
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!! said:
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

:

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


:

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

:

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


:

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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top