Value if no date

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a report based on a query. There is a text box
(txtComplete) for completion date [Complete]. If
[Complete] is blank, I would like to see "Not Complete",
with the date appearing otherwise. I tried:
=IIf(IsNull([Complete]),"Not Complete",Format
([Complete],"m/d/yy")), but I received #Error in every
record on the report, whether or not the [Complete] was
empty. I tried replacing the else part of the expression
with "Complete", and saw "Complete" in every field, even
the empty ones. I also tried "Short Date" instead
of "m/d/yy", but no change.
[Complete] is a date field in the query's underlying table.
I went with Format(etc.) for Else with the idea that Then
and Else would be the same type of expression (i.e. a
string).
What am I missing here?
 
use the ISError function, then do a IIF on the result
ISError[Complete] will return a -1 if there is an error
example:
Text100: Iserror([Complete])
IIF([Text100]=-1,"Not Complete",[Complete])

then do an IIF on the
 
I believe that in the case where Then is a string, Else
needs to be a string also (or maybe a text field), but not
a date field. Leaving that aside, when I realized that
your suggestion would not readily work in a text box
(which is where my formula was located), I got onto a
different thought track and decided to try the formula in
a calculated field in the query that is the report's
source. For whatever reason that worked. All I had to do
was point the text box to that field.
-----Original Message-----
use the ISError function, then do a IIF on the result
ISError[Complete] will return a -1 if there is an error
example:
Text100: Iserror([Complete])
IIF([Text100]=-1,"Not Complete",[Complete])

then do an IIF on the
-----Original Message-----
I have a report based on a query. There is a text box
(txtComplete) for completion date [Complete]. If
[Complete] is blank, I would like to see "Not Complete",
with the date appearing otherwise. I tried:
=IIf(IsNull([Complete]),"Not Complete",Format
([Complete],"m/d/yy")), but I received #Error in every
record on the report, whether or not the [Complete] was
empty. I tried replacing the else part of the expression
with "Complete", and saw "Complete" in every field, even
the empty ones. I also tried "Short Date" instead
of "m/d/yy", but no change.
[Complete] is a date field in the query's underlying table.
I went with Format(etc.) for Else with the idea that Then
and Else would be the same type of expression (i.e. a
string).
What am I missing here?
.
Click to expand...
.
Click to expand...
 
Normally this is caused by the name of the text box being the same as the
name of a field.
 
Thanks for the reply. Indeed that was the case. This was
an early database, before I had learned the value of
naming conventions. It didn't even occur to me to check,
since now I never use the default name (the field name)
for the name of a control. It should have been the first
thing I checked. Maybe if it wasn't Friday I would have
thought of that. As it happens, I put the calculation
into the report's source query, where it works fine.
-----Original Message-----
Normally this is caused by the name of the text box being the same as the
name of a field.

--
Duane Hookom
MS Access MVP


I have a report based on a query. There is a text box
(txtComplete) for completion date [Complete]. If
[Complete] is blank, I would like to see "Not Complete",
with the date appearing otherwise. I tried:
=IIf(IsNull([Complete]),"Not Complete",Format
([Complete],"m/d/yy")), but I received #Error in every
record on the report, whether or not the [Complete] was
empty. I tried replacing the else part of the expression
with "Complete", and saw "Complete" in every field, even
the empty ones. I also tried "Short Date" instead
of "m/d/yy", but no change.
[Complete] is a date field in the query's underlying table.
I went with Format(etc.) for Else with the idea that Then
and Else would be the same type of expression (i.e. a
string).
What am I missing here?
Click to expand...


.
Click to expand...
 
Back
Top