Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Difference (Tricky) (2K)

    I'm trying to run a date difference calculation on a report (see Post 273665). The report and query have been set up and return requested records.

    The following would ONLY apply when [maintbl].[ID_status] equals 4 (All other records would still need to be on the report, however they would not need the date difference calculation)
    [maintbl].[prelim_sufficent] is a check box. If TRUE, then [prelimsent_date] minus [incident_date] OR
    [maintbl].[further_investigation] is a check box. If TRUE, then [finalsent_date] minus [incident_date]

    While DateDiff may work, I'm not sure of how to work in the nifty GetWorkDays Function (provided by MarkD). I've attached the Function. Please let me know if you require additional info.

    Thanks!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Difference (Tricky) (2K)

    What should happen if prelim_sufficent and further_investigation are both True or both False? For check boxes, the conventional behavior is that they can be on/off independent;y of each other (while radio buttons act as a group - if one is on, the others are off.)

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference (Tricky) (2K)

    Hans-
    In some cases, both will be off. However -- when [ID_status] equals 4, one or the other will be ON.

    This Date Difference will only be done for those records where [ID_status] is equal to 4.

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Difference (Tricky) (2K)

    If one or the other will always be true, there is no need to check both.

    Difference: GetWorkDays([incident_date],IIf([maintbl].[prelim_sufficent],[prelimsent_date],[finalsent_date]),False)

    The first argument to GetWorkDays (the start date) is always [incident_date]. The second argument (the end date) is [prelimsent_date] if prelim_sifficent is true, otherwise it is [finalsent_date]. I don't know if you have created a table with holidays named tblHolidays; if so, replace False with True as the last argument to GetWorkDays; it specifies that you want to take holidays into account.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference (Tricky) (2K)

    In a report, I have created an unbound text box. As I do have a table named tblHolidays, I've used your suggestion to change False to True. After making sure my values are spelled right, I've placed the following into the Control Source:
    =GetWorkDays([incident_date],IIf([incident_maintbl].[prelim_sufficent],[prelimsenttodirector_date],[finalsenttodirector_date]),True)

    The query bound to the report is a Parameter Query asking for StartDate and EndDate for incident_date. With the statement above, I'm asked for StartDate and EndDate (which is expected) and then incident_maintbl, prelimsenttodirector_date (which is unexpected). So I guess I'm asking, where do I put this statement (query, form, report) and just what should it say? Also, won't there be an error on those records with no dates (due to not being [ID_status] = 4)? How can I not get the Error# on the report?

    Also, all records will have a date value (when [ID_status] = 4) for [prelimsenttodirector_date]. Only when [further_investigation] = True will the [finalsenttodirector_date] be used for time span.

    Thanks! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Difference (Tricky) (2K)

    The names of tables and fields seem to change between posts in this thread, making it difficult to judge what is correct and what might be a typo. I don't know the actual names, you'll have to check that yourself.

    You can add a check for ID_Status:

    =IIf([ID_Status]=4,GetWorkDays([incident_date],IIf([incident_maintbl].[prelim_sufficent],[prelimsenttodirector_date],[finalsenttodirector_date]),True),Null)

    If your earlier assertions are correct, further_investigation should be True if prelim_sufficient is False (and ID_Status=4), so finalsenttodirector_date should be filled then.

  7. #7
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference (Tricky) (2K)

    Hans-
    The field/table names are correct. My assertions are correct and it appears the statement takes all conditions into account. Pasted the suggested statement into the Control Source of the Unbound text box.

    Still getting asked (via Parameter Value request) to enter something for [incident_maintbl]. I left it blank and clicked OK. Every record had #Name? in the calculation field.

    Tried taking out the reference to the table, thinking that since the query bound to the report pulls all of the variable requested in the statement, perhaps I don't need to be so specific. That eliminated the request for a Parameter Value for [incident_maintbl]. Instead, I was prompted (again via Parameter Value request) to enter something for [prelim_sufficent]. I left the Value blank by pressing OK.

    My report ran and I was pleased to see a blank for records where [ID_status] DOES NOT = 4. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    For those records with [further_investigation]=True, there was a valid day span (w/o weekends and holidays) <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    For those records with [prelim_sufficient]=True, there was #Error <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    I swear Hans, I've double checked my spelling and it's all right-on.

    Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Difference (Tricky) (2K)

    Are you really really really sure about the spelling? You reply mentions both

    <big><big><big>[prelim_sufficent]</big></big></big>

    and

    <big><big><big>[prelim_sufficient]</big></big></big>

    Notice the extra <big><big><big>i</big></big></big>

  9. #9
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference (Tricky) (2K)

    It was MY error.
    Works like a charm.

    Thanks Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •