Calculating differences in Access

[annie techwriter]

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

 

[fredg]

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

 

[annie techwriter]

I think that's where I"m running into the issue. The [Date Closed] may or may
not have a date in it yet (if the issue is not closed, there would be no
date).

Is there any way around this?

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

 

[fredg]

I think that's where I"m running into the issue. The [Date Closed] may or may
not have a date in it yet (if the issue is not closed, there would be no
date).

Is there any way around this?

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

 

[annie techwriter]

No. When I used that expression instead of the
=DateDiff("d",[firstReported],[DateClosed]) expression it did not make a
difference. I still received #Error when the [DateClosed] was blank.

I think that's where I"m running into the issue. The [Date Closed] may or may
not have a date in it yet (if the issue is not closed, there would be no
date).

Is there any way around this?

On Wed, 17 Sep 2008 10:22:00 -0700, annie techwriter wrote:

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

 

[fredg]

No. When I used that expression instead of the
=DateDiff("d",[firstReported],[DateClosed]) expression it did not make a
difference. I still received #Error when the [DateClosed] was blank.

I think that's where I"m running into the issue. The [Date Closed] may or may
not have a date in it yet (if the issue is not closed, there would be no
date).

Is there any way around this?

:

On Wed, 17 Sep 2008 10:22:00 -0700, annie techwriter wrote:

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

1) Make sure the name of this control is not the same as the name of
any field used in the control source expressioon, i.e. not
"DateClosed" or "FirstReported".

2) It not #1 above, then you have either done something incorrectly or
you have left out some other necessary information.

The above expression, on a form, works for me. It shows either the
difference between 2 dates, or is blank if "DateClosed" is null.

 

[annie techwriter]

Thank you for the help fredg.

1) the name that displays when I open the properties is 'Cycle Date'. I
assume that's what you mean by the 'name of this control'. If I'm looking at
the wrong name, please let me know.

No. When I used that expression instead of the
=DateDiff("d",[firstReported],[DateClosed]) expression it did not make a
difference. I still received #Error when the [DateClosed] was blank.

On Wed, 17 Sep 2008 12:33:02 -0700, annie techwriter wrote:

I think that's where I"m running into the issue. The [Date Closed] may or may
not have a date in it yet (if the issue is not closed, there would be no
date).

Is there any way around this?

:

On Wed, 17 Sep 2008 10:22:00 -0700, annie techwriter wrote:

Hello,

I am calculating the number of days between an issue being Opened and
Closed. That part works fine, but if I do not have a Closed date yet, the
Number of Days field shows #Error.

This is what I have:
=DateDiff("d",[firstReported],[DateClosed])

What can I add to have the field display nothing if the DateClosed is null?

Thanks for any help you can give me.

Test to see if the [DateClosed] has any data first.
In an Unbound control on your form or report (NOT in a table):

=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))

1) Make sure the name of this control is not the same as the name of
any field used in the control source expressioon, i.e. not
"DateClosed" or "FirstReported".

2) It not #1 above, then you have either done something incorrectly or
you have left out some other necessary information.

The above expression, on a form, works for me. It shows either the
difference between 2 dates, or is blank if "DateClosed" is null.

 

[fredg]

Thank you for the help fredg.

1) the name that displays when I open the properties is 'Cycle Date'. I
assume that's what you mean by the 'name of this control'. If I'm looking at
the wrong name, please let me know.

:

*** snipped ***

If you are looking at the control's Property sheet's Other tab, Name
property line, you are correct. "Cycle Date" should not cause #error
in this case as there is no field [Cycle Date] used in the expression.
Note: It's not a good idea to include spaces in field or table names.
"CycleDate" is as easy to read as "Cycle Date", and can be used
without the brackets.

If you use an expression as the control source, the name of that
control must not be the same as the name of any of the fields in the
expression. Access gets confused. That's why I always recommend using
an unbound control whenever you need to use an expression in a control
source.
Oftentimes a user will take a bound control (let's say bound to the
[firstReported] field. Access will, by default, name this control
"firstReported". This does not cause a problem.
However, if the user then changes the control source to an expression,
such as:
= DateDiff("d",[firstReported],[DateClosed])
and the name of the control has not been changed, Access will give an
#error.

Are you absolutely sure [firstReported] and [DateClosed] are DateTime
datatype fields, and not Text datatypes?
What do you get if you change the expression to:

=IIf(IsNull([DateClosed]),"Nothing here",
DateDiff("d",[firstReported],[DateClosed]))

If the [DateClosed] is null "Nothing here" should display.

If you still need help, please copy and paste into your message the
complete "exact" control source expression. You could have left off a
parentheses or something.

 

[annie techwriter]

I have no idea what was different, but I inserted the
=IIf(IsNull([DateClosed]),"Nothing here",
DateDiff("d",[firstReported],[DateClosed]))
expression and Nothing here did display as appropriate. I changed "Nothing
here" to " " and it seems to work perfectly.

Thank you!!!

Thank you for the help fredg.

1) the name that displays when I open the properties is 'Cycle Date'. I
assume that's what you mean by the 'name of this control'. If I'm looking at
the wrong name, please let me know.

:

*** snipped ***

If you are looking at the control's Property sheet's Other tab, Name
property line, you are correct. "Cycle Date" should not cause #error
in this case as there is no field [Cycle Date] used in the expression.
Note: It's not a good idea to include spaces in field or table names.
"CycleDate" is as easy to read as "Cycle Date", and can be used
without the brackets.

If you use an expression as the control source, the name of that
control must not be the same as the name of any of the fields in the
expression. Access gets confused. That's why I always recommend using
an unbound control whenever you need to use an expression in a control
source.
Oftentimes a user will take a bound control (let's say bound to the
[firstReported] field. Access will, by default, name this control
"firstReported". This does not cause a problem.
However, if the user then changes the control source to an expression,
such as:
= DateDiff("d",[firstReported],[DateClosed])
and the name of the control has not been changed, Access will give an
#error.

Are you absolutely sure [firstReported] and [DateClosed] are DateTime
datatype fields, and not Text datatypes?
What do you get if you change the expression to:

=IIf(IsNull([DateClosed]),"Nothing here",
DateDiff("d",[firstReported],[DateClosed]))

If the [DateClosed] is null "Nothing here" should display.

If you still need help, please copy and paste into your message the
complete "exact" control source expression. You could have left off a
parentheses or something.