Calculating differences in Access

  • Thread starter Thread starter annie techwriter
  • Start date Start date
A

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.
 
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.
Click to expand...

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

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

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]))
Click to expand...
 
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?

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

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]))
Click to expand...
Click to expand...

Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))
 
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.


fredg said:
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?

fredg said:
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]))
Click to expand...
Click to expand...

Didn't my expression work?
=IIf(IsNull([DateClosed]),Null,DateDiff("d",[firstReported],[DateClosed]))
Click to expand...
 
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.

fredg said:
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]))
Click to expand...

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

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

fredg said:
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.

fredg said:
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]))
Click to expand...
Click to expand...

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.
Click to expand...
 
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.

:
Click to expand...
*** 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.
 
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!!!

fredg said:
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.

:
Click to expand...
*** 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.
Click to expand...
 
Back
Top