If Null - Display Text

[SarahJ]

I have a report that tracks the dates certain events happen.

i.e.
Date Request received
Date sent out
Approval date
etc...

How do I get the report to display the text "Not Available" if there is no
date recorded in the table?

 

[Rob Parker]

The NZ function is your friend here ;-)

The Nz function replaces nulls with zero by default, but can be used to
replace a null with any value/string you want. So, to display "Not
Available" if a field named RequestDate is null, you enter the expression
=Nz([RequestDate],"Not Available")
in the textbox which is displaying the [RequestDate] field.

Important Note: the textbox control must NOT be named RequestDate (its
default name if you built the form/report by drag/dropping fields, using the
wizard, ...). Ensure that it is named something like txtRequestDate. If
the textbox has the same name as the field name in the Nz expression, all it
will display is #error.

HTH,

Rob

 

[Duane Hookom]

I would not do anything with the Control Source property. You can set the
Format property of the text box to display "Not Available" if the value is
null.
Format: m/d/yyyy;;;"Not Available"
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The NZ function is your friend here ;-)

The Nz function replaces nulls with zero by default, but can be used to
replace a null with any value/string you want. So, to display "Not
Available" if a field named RequestDate is null, you enter the expression
=Nz([RequestDate],"Not Available")
in the textbox which is displaying the [RequestDate] field.

Important Note: the textbox control must NOT be named RequestDate (its
default name if you built the form/report by drag/dropping fields, using the
wizard, ...). Ensure that it is named something like txtRequestDate. If
the textbox has the same name as the field name in the Nz expression, all it
will display is #error.

HTH,

Rob

I have a report that tracks the dates certain events happen.

i.e.
Date Request received
Date sent out
Approval date
etc...

How do I get the report to display the text "Not Available" if there is no
date recorded in the table?

 

[Rob Parker]

Thanks Duane,

I don't very often use the Format property, so I overlooked the obvious.

Rob

I would not do anything with the Control Source property. You can set the
Format property of the text box to display "Not Available" if the value is
null.
Format: m/d/yyyy;;;"Not Available"
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The NZ function is your friend here ;-)

The Nz function replaces nulls with zero by default, but can be used to
replace a null with any value/string you want. So, to display "Not
Available" if a field named RequestDate is null, you enter the expression
=Nz([RequestDate],"Not Available")
in the textbox which is displaying the [RequestDate] field.

Important Note: the textbox control must NOT be named RequestDate (its
default name if you built the form/report by drag/dropping fields, using
the
wizard, ...). Ensure that it is named something like txtRequestDate. If
the textbox has the same name as the field name in the Nz expression, all
it
will display is #error.

HTH,

Rob

I have a report that tracks the dates certain events happen.

i.e.
Date Request received
Date sent out
Approval date
etc...

How do I get the report to display the text "Not Available" if there is
no
date recorded in the table?

 

[Duane Hookom]

The format property isn't very obvious since this only works for specific
data types (numbers and dates).

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thanks Duane,

I don't very often use the Format property, so I overlooked the obvious.

Rob

I would not do anything with the Control Source property. You can set the
Format property of the text box to display "Not Available" if the value is
null.
Format: m/d/yyyy;;;"Not Available"
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The NZ function is your friend here ;-)

The Nz function replaces nulls with zero by default, but can be used to
replace a null with any value/string you want. So, to display "Not
Available" if a field named RequestDate is null, you enter the expression
=Nz([RequestDate],"Not Available")
in the textbox which is displaying the [RequestDate] field.

Important Note: the textbox control must NOT be named RequestDate (its
default name if you built the form/report by drag/dropping fields, using
the
wizard, ...). Ensure that it is named something like txtRequestDate. If
the textbox has the same name as the field name in the Nz expression, all
it
will display is #error.

HTH,

Rob

I have a report that tracks the dates certain events happen.

i.e.
Date Request received
Date sent out
Approval date
etc...

How do I get the report to display the text "Not Available" if there is
no
date recorded in the table?

 

[John Spencer]

Actually it should also work with text, since you can specify a null
format for text strings.


As in the following will show "This is blank" in the control IF the
control's value is null

@;"This is blank."

 

[Duane Hookom]

Thanks John. I wasn't aware of the ability to do this with text!

 

[John Spencer]

You're welcome. A small payback for all the tricks and tips I have picked
up from you.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..