If Null - Display Text

  • Thread starter Thread starter SarahJ
  • Start date Start date
S

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?
 
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 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


Rob Parker said:
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

SarahJ said:
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?
Click to expand...
Click to expand...
 
Thanks Duane,

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

Rob

Duane Hookom said:
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


Rob Parker said:
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

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


Rob Parker said:
Thanks Duane,

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

Rob

Duane Hookom said:
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


Rob Parker said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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."
 
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
..
 
Back
Top