Printing parameter value on report

[WCDoan]

I've been reading the prior posts to try to find an answer, but failed to do
so. Thought I had it, but turned out I didn't. I have a report that is based
on a select query that asks the user to [Enter Destruction Date:]. This date
field is a text field. The reason being I was told to create a database for
archiving records that have been boxed and labeled. The destruction date was
put on the label in a variety of ways; Jan 2007, 01-01-07, January 1, 2007,
etc. If the user enters a date that has no records, I would like to print 'No
Records for.....whatever they entered' on the report. I thought I could use
an Iff statement as the control source for a text box along the lines of Iff
([OnNoData]), "No Records for [Enter Destruction Date:]", [Visible]=False). I
know the quotes are wrong in the true part, I know there's suppose to be an
ampersand, I just don't know where and how to put them. I thought that if
there were no records the true part would print and if there were records
then it wouldn't. However, I get the 'No records....." whether there are
matching records or not. Unfortunately, I don't use Access enough to get
totally fluent with it being that I work for a small town and have to do many
different things...grant writing, web content, etc and may go months without
using it, which is the case now. So, I come here for help and here I am
again.
Thanks,
RandyM

 

[Marshall Barton]

I've been reading the prior posts to try to find an answer, but failed to do
so. Thought I had it, but turned out I didn't. I have a report that is based
on a select query that asks the user to [Enter Destruction Date:]. This date
field is a text field. The reason being I was told to create a database for
archiving records that have been boxed and labeled. The destruction date was
put on the label in a variety of ways; Jan 2007, 01-01-07, January 1, 2007,
etc. If the user enters a date that has no records, I would like to print 'No
Records for.....whatever they entered' on the report. I thought I could use
an Iff statement as the control source for a text box along the lines of Iff
([OnNoData]), "No Records for [Enter Destruction Date:]", [Visible]=False). I
know the quotes are wrong in the true part, I know there's suppose to be an
ampersand, I just don't know where and how to put them. I thought that if
there were no records the true part would print and if there were records
then it wouldn't. However, I get the 'No records....." whether there are
matching records or not.

You need to check the HasData property and can not make the
text box invisible. Try this instead:

=IIf(HasData, Null, "No Records for " & [Enter Destruction
Date:]")

 

[WCDoan]

Marshall,
Thanks for the reply. Part of it worked great, but the parameter value
still does not display. I get an #Error when I enter a date that has no
records for destruction. When I enter a date that does, the "No records...."
doesn't show which is what I wanted to happen. Any ideas why the parameter
value won't display. I'm beginning to think it's in the query somewhere
because when I add a text box and use the [Enter Destruction Date:] as the
control source it says it's not in the field list and it's not. Should the
parameter value from the query be in the field list? Color me dazed and
confused.
RandyM

I've been reading the prior posts to try to find an answer, but failed to do
so. Thought I had it, but turned out I didn't. I have a report that is based
on a select query that asks the user to [Enter Destruction Date:]. This date
field is a text field. The reason being I was told to create a database for
archiving records that have been boxed and labeled. The destruction date was
put on the label in a variety of ways; Jan 2007, 01-01-07, January 1, 2007,
etc. If the user enters a date that has no records, I would like to print 'No
Records for.....whatever they entered' on the report. I thought I could use
an Iff statement as the control source for a text box along the lines of Iff
([OnNoData]), "No Records for [Enter Destruction Date:]", [Visible]=False). I
know the quotes are wrong in the true part, I know there's suppose to be an
ampersand, I just don't know where and how to put them. I thought that if
there were no records the true part would print and if there were records
then it wouldn't. However, I get the 'No records....." whether there are
matching records or not.

You need to check the HasData property and can not make the
text box invisible. Try this instead:

=IIf(HasData, Null, "No Records for " & [Enter Destruction
Date:]")

 

[Marshall Barton]

I apologize for wasting your time. You're right, what I
posted won't work for this situation.

This is another case where using a query parameter prompt is
inadequate and it's time for you to move up a level.
Instead of using a prompt string to enter the filter value,
use a form with a text box for users to enter the date
string and a button to open the report. In this arrangement
the query parameter would look like:
Forms![the form name].[the text box name]
Because your parameter might look like a date, you should do
something to tell Access it's text string. You can by just
setting the text box's Format property to a text format such
as @

With all that in place the report text box would be
=IIf(HasData, Null, "No Records for " &Forms![the form
name].[the text box name])

In case you are interested, the next level of
report-query-parameters is to remove the parameter from the
query and use the OpenReport method's WhereCondition
argument. The report text box would remain the same.
--
Marsh
MVP [MS Access]

Thanks for the reply. Part of it worked great, but the parameter value
still does not display. I get an #Error when I enter a date that has no
records for destruction. When I enter a date that does, the "No records...."
doesn't show which is what I wanted to happen. Any ideas why the parameter
value won't display. I'm beginning to think it's in the query somewhere
because when I add a text box and use the [Enter Destruction Date:] as the
control source it says it's not in the field list and it's not. Should the
parameter value from the query be in the field list? Color me dazed and
confused.
RandyM

I've been reading the prior posts to try to find an answer, but failed to do
so. Thought I had it, but turned out I didn't. I have a report that is based
on a select query that asks the user to [Enter Destruction Date:]. This date
field is a text field. The reason being I was told to create a database for
archiving records that have been boxed and labeled. The destruction date was
put on the label in a variety of ways; Jan 2007, 01-01-07, January 1, 2007,
etc. If the user enters a date that has no records, I would like to print 'No
Records for.....whatever they entered' on the report. I thought I could use
an Iff statement as the control source for a text box along the lines of Iff
([OnNoData]), "No Records for [Enter Destruction Date:]", [Visible]=False). I
know the quotes are wrong in the true part, I know there's suppose to be an
ampersand, I just don't know where and how to put them. I thought that if
there were no records the true part would print and if there were records
then it wouldn't. However, I get the 'No records....." whether there are
matching records or not.

You need to check the HasData property and can not make the
text box invisible. Try this instead:

=IIf(HasData, Null, "No Records for " & [Enter Destruction
Date:]")

 

[WCDoan]

Marshall, thanks for continuing the help and believe me, you did not waste my
time, I hope I'm not wasting yours. I'll look into using the info you gave me
and see what happens. But, having a curious nature, I'm interested in why
this parameter value won't work. I don't understand what makes this different
from something like [Start Date]. I've used these before and had no problem
referencing them. The query works the way it should, I just can't understand
why it won't print. Thanks again for your suggestion and for your previous
help in my pursuit of a solution for this problem.
RandyM

I apologize for wasting your time. You're right, what I
posted won't work for this situation.

This is another case where using a query parameter prompt is
inadequate and it's time for you to move up a level.
Instead of using a prompt string to enter the filter value,
use a form with a text box for users to enter the date
string and a button to open the report. In this arrangement
the query parameter would look like:
Forms![the form name].[the text box name]
Because your parameter might look like a date, you should do
something to tell Access it's text string. You can by just
setting the text box's Format property to a text format such
as @

With all that in place the report text box would be
=IIf(HasData, Null, "No Records for " &Forms![the form
name].[the text box name])

In case you are interested, the next level of
report-query-parameters is to remove the parameter from the
query and use the OpenReport method's WhereCondition
argument. The report text box would remain the same.
--
Marsh
MVP [MS Access]

Thanks for the reply. Part of it worked great, but the parameter value
still does not display. I get an #Error when I enter a date that has no
records for destruction. When I enter a date that does, the "No records...."
doesn't show which is what I wanted to happen. Any ideas why the parameter
value won't display. I'm beginning to think it's in the query somewhere
because when I add a text box and use the [Enter Destruction Date:] as the
control source it says it's not in the field list and it's not. Should the
parameter value from the query be in the field list? Color me dazed and
confused.
RandyM

WCDoan wrote:

I've been reading the prior posts to try to find an answer, but failed to do
so. Thought I had it, but turned out I didn't. I have a report that is based
on a select query that asks the user to [Enter Destruction Date:]. This date
field is a text field. The reason being I was told to create a database for
archiving records that have been boxed and labeled. The destruction date was
put on the label in a variety of ways; Jan 2007, 01-01-07, January 1, 2007,
etc. If the user enters a date that has no records, I would like to print 'No
Records for.....whatever they entered' on the report. I thought I could use
an Iff statement as the control source for a text box along the lines of Iff
([OnNoData]), "No Records for [Enter Destruction Date:]", [Visible]=False). I
know the quotes are wrong in the true part, I know there's suppose to be an
ampersand, I just don't know where and how to put them. I thought that if
there were no records the true part would print and if there were records
then it wouldn't. However, I get the 'No records....." whether there are
matching records or not.


You need to check the HasData property and can not make the
text box invisible. Try this instead:

=IIf(HasData, Null, "No Records for " & [Enter Destruction
Date:]")

 

[Marshall Barton]

Marshall, thanks for continuing the help and believe me, you did not waste my
time, I hope I'm not wasting yours. I'll look into using the info you gave me
and see what happens. But, having a curious nature, I'm interested in why
this parameter value won't work. I don't understand what makes this different
from something like [Start Date]. I've used these before and had no problem
referencing them. The query works the way it should, I just can't understand
why it won't print. Thanks again for your suggestion and for your previous
help in my pursuit of a solution for this problem.

Most people do not use parameter prompts so the nuances are
not widely known (for sure not to me). OTOH, this looks
like the standard situation where a query with no records
has no values. Sort of like asking for the value of a field
in the first record when there are no records. It's not
always clear what happens in that kind of situation so
trying to rely on anything is a risky (depends on unknown
factors) thing to do. I ran a bunch of tests and it
appears(?) that a reference to parameter in a report query
with no data is #Error, i.e. IsError([Start Date]) = True

I suspect that if you went back to your [Start Date] that
you think works and tried using a parameter value of
12/31/2500, you would see the same kind of problem.

When using a form text box for the parameter, at least the
text box exists and has a value. It is also a more user
friendly UI that avoids things poping up on the screen.