|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 17th, 2008, 06:22 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Calculating differences in Access
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. 
|
|
#2
September 17th, 2008, 07:12 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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
|
|
#3
September 17th, 2008, 08:33 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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" wrote: 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
|
|
#4
September 17th, 2008, 09:18 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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? "fredg" wrote: 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])) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#5
September 17th, 2008, 09:50 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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" wrote: 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? "fredg" wrote: 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])) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#6
September 17th, 2008, 10:26 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
On Wed, 17 Sep 2008 13:50:01 -0700, annie techwriter wrote:
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" wrote: 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? "fredg" wrote: 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])) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#7
September 18th, 2008, 04:06 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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" wrote: On Wed, 17 Sep 2008 13:50:01 -0700, annie techwriter wrote: 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" wrote: 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? "fredg" wrote: 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])) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#8
September 18th, 2008, 05:38 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
On Thu, 18 Sep 2008 08:06:00 -0700, annie techwriter wrote:
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" wrote: *** 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#9
September 18th, 2008, 06:46 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Calculating differences in Access
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" wrote: On Thu, 18 Sep 2008 08:06:00 -0700, annie techwriter wrote: 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" wrote: *** 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
