If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Empty date field error
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 |
#3
|
|||
|
|||
Empty date field error
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 |
#4
|
|||
|
|||
Empty date field error
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 |
#5
|
|||
|
|||
Empty date field error
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 |
#6
|
|||
|
|||
Empty date field error
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 |
#7
|
|||
|
|||
Empty date field error
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 |
#8
|
|||
|
|||
Empty date field error
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 |
#9
|
|||
|
|||
Empty date field error
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 |
#10
|
|||
|
|||
Empty date field error
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 |
|
Thread Tools | |
Display Modes | |
|
|