Calculating Days for Order Turnaround.

[Access Idiot]

Hi, i have a field for the open date on the purchase order, and the close
date, if the close date is blank I want it to say ON ORDER in the turnaround
time field, otherwise I want the amount of days between the dates. The
following code puts in ON ORDER when close date is blank, but does not
calculate the field when the close date is populated.

=IIf(([dtmdateclosed]=" "),"On Order",([dtmDateClosed]-[dtmDateOpen])/365)

Thanks for any help.
Todd

 

[Allen Browne]

If there no value in the field, it is not equal to a space.
The value is Null, and you test it with IsNull().

Try:
=IIf(IsNull([dtmdateclosed]),"On Order", [dtmDateClosed] - [dtmDateOpen])

Alternatively, use Nz() to convert the null to the text you want:
=Nz([dtmDateClosed] - [dtmDateOpen], "On Order")

(Note that I removed the "/365" bit as you said you wanted the difference in
days.)

 

[Access Idiot]

Thanks Allen, but the issue seems to be with the other part, I get the "On
Order" in the field when there is no date ( that works ), however when their
is a date, I just get "#error" in the field.

If there no value in the field, it is not equal to a space.
The value is Null, and you test it with IsNull().

Try:
=IIf(IsNull([dtmdateclosed]),"On Order", [dtmDateClosed] - [dtmDateOpen])

Alternatively, use Nz() to convert the null to the text you want:
=Nz([dtmDateClosed] - [dtmDateOpen], "On Order")

(Note that I removed the "/365" bit as you said you wanted the difference in
days.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, i have a field for the open date on the purchase order, and the close
date, if the close date is blank I want it to say ON ORDER in the
turnaround
time field, otherwise I want the amount of days between the dates. The
following code puts in ON ORDER when close date is blank, but does not
calculate the field when the close date is populated.

=IIf(([dtmdateclosed]=" "),"On Order",([dtmDateClosed]-[dtmDateOpen])/365)

Thanks for any help.
Todd

 

[Allen Browne]

Remove anything from the Format property of the text box.

Check that the Name of the control is not the same as any control.
For example, it must not be called dtmDateClosed.

What are dtmDateClosed and dtmDateOpen? Are they date/time fields in a
table? Calculated fields in a query? Access may be misunderstanding the data
type and therefore unable to handle the math. Try typecasting them, and
using DateDiff().

The issue might be that the expression is sometimes text and sometimes
numeric. Try:
=IIf(IsNull([dtmdateclosed]),"On Order", DateDiff("d", [dtmDateOpen],
[dtmDateClosed]) & " days")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but the issue seems to be with the other part, I get the "On
Order" in the field when there is no date ( that works ), however when
their
is a date, I just get "#error" in the field.

If there no value in the field, it is not equal to a space.
The value is Null, and you test it with IsNull().

Try:
=IIf(IsNull([dtmdateclosed]),"On Order", [dtmDateClosed] - [dtmDateOpen])

Alternatively, use Nz() to convert the null to the text you want:
=Nz([dtmDateClosed] - [dtmDateOpen], "On Order")

(Note that I removed the "/365" bit as you said you wanted the difference
in
days.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, i have a field for the open date on the purchase order, and the
close
date, if the close date is blank I want it to say ON ORDER in the
turnaround
time field, otherwise I want the amount of days between the dates. The
following code puts in ON ORDER when close date is blank, but does not
calculate the field when the close date is populated.

=IIf(([dtmdateclosed]=" "),"On
Order",([dtmDateClosed]-[dtmDateOpen])/365)

Thanks for any help.
Todd

 

[Access Idiot]

It was in the tables! One was set to Text. Always the overlooked.
Thanks for your help!

Todd