Calculating Days on Order, formula wont work with Text Values

[Guest]

Good Day,
I have a Database I am using to track uniform orders.
I have fields to show when the order was opened, when it was closed ( if it
has been closed ), and how long the order took to process.
If no close date for the order has been entered I wanted the fields to show
"On order", therefore I used an nz statement in them i.e.
=nz([dtmdateclosed],"On Order") because I needed them to return a null for
this to work I had to set the field types for the date fields as text. Now
all the fields work except the 3rd one, which calculated the days on order.
I have =nz([dtmdateclosed]-[dtmdateopen],"On Order") but it produces an error.
I also tried an IIF statement, but these fields simply dont want to work for
me, I suspect it is that they are a text type, but do not know how to fix it.
Any help greatly apprecaiated.
Todd

 

[Marshall Barton]

I have a Database I am using to track uniform orders.
I have fields to show when the order was opened, when it was closed ( if it
has been closed ), and how long the order took to process.
If no close date for the order has been entered I wanted the fields to show
"On order", therefore I used an nz statement in them i.e.
=nz([dtmdateclosed],"On Order") because I needed them to return a null for
this to work I had to set the field types for the date fields as text. Now
all the fields work except the 3rd one, which calculated the days on order.
I have =nz([dtmdateclosed]-[dtmdateopen],"On Order") but it produces an error.
I also tried an IIF statement, but these fields simply dont want to work for
me, I suspect it is that they are a text type, but do not know how to fix it.

Don't mess with a field's type just to make it look good.
Formatting data for human consumption should be delayed
until the data is actually being displayed on a form (or,
ugh, a query).

Change the fields back to Date type and then set the Format
property to a custom format like:
d mmm", "yyyy;;;"On Order"