Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11

    Question Unanswered: Conditional Due Date Problem

    Okay. In my company, we get forms faxed in describing adverse medical events. These forms are updated and faxed in whenever new information about the event becomes available. Whenever we receive a fax, I have either 3 days or 7 days, depending on whether the event was fatal or not, to draft a narrative to hand in. The fields I have so far are: Initial report date, Follow-up #1 date ... Follow-up #8 date, Fatal (meaning 3 day narrative), Due Date. Note: Fatal is a yes/no field.

    Problem: I want the due date calculated on a) the date the fax comes in and b) whether it's a 3 day or a 7 day affair.

    I've been working on nested IIf statements and DateAdd expressions and have been coming up with errors left right and center. The closest I came was:

    IIf ([Follow-up #1] = Null, (IIf ([Fatal] = true, DateAdd("d", 3, [Initial Report Date]), DateAdd("d", 7, [Initial Report Date])),(IIf ([Fatal] = true, DateAdd("d",3, [Follow-up #1]), DateAdd("d", 7, [Follow-up #1]+7)))

    ...then I need to continue with the same pattern so it will check follow-up2 Date, and if it's blank to use the follow-up 1 date and so on....

    Is this calculation even possible? Or am I making this just way too complicated? ***ANY*** help would be greatly appreciated. Thanks.
    Last edited by bruch04; 03-12-04 at 13:40.

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    I recommend no # in your name use an "_" instead. This may make your errors disappear. "#" are used before and after numbers to signify a valid date, and in code to help format display of numbers. For instance, #6/15/2002# and format(12345,"#,##.0") yields 12,345.0.

    Where are you using your equation? In a txtbox?

  3. #3
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11
    I'm using it in the control source box when you right-click on a form field and choose "properties".

    I tried removing the #signs from the field titles, but came up with an error saying I have invalid syntax, and that my text data needs to be enclosed in quotes, but none of my fields contains text data. They're all in date/time format so they can be used in calculations.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Just to clarify before I try to dig into a solution..

    You have one table with one record for each incident. There are 8 follow up dates for this incident that are either 3 or 7 days apart depending on the whether or not the incident is fatal, which is stored as a bit.

    eg. if you had an incident on 1-1 of a given month that was non-fatal it would be:

    followup 1: 1-8
    followup 2: 1-15
    followup 3: 1-22

    And if it was fatal it would be:

    followup 1: 1-4
    followup 2: 1-7
    followup 3: 1-10

    Is this correct?

  5. #5
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11

    Smile

    Mmmm...not quite. You've got the first part right, in that I want one record for each event, with an initial report date and room for up to eight follow-ups. You never know when an update is going to come in. And I'm not sure what you mean by "1-1" and so on, but say a non-fatal event happens and they fax it in on 05Jan04. Due date for that initial report would be 12Jan04. Time passes and we get another fax with updated information on the event, say on 16Feb04. Due date for a second narrative would now be 23Feb04. Say the medical condition ultimately proves to be fatal, and we get a fax in on 04June04 to that effect. Due date for this third follow up, which is now a fatal case, is 07June04. I want one "Due Date" field to show when this case is due, depending on where it is in the process.

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    aha, now i figured out what you want:

    Event [x]
    Due_Date:
    followup_1
    ...
    foollowup_8

    are there only 8? there can only be a max of eight followups?
    is this an e.g of a typical record?

    i understand what your case is; could you send me a pic of the layout of the fields in your table; this might require some coding;

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well in that case, I would strongly recommend a change in your database schema. I would create two tables, one with

    incident_id
    initial_report_date
    memo
    fatal

    Then another table for all of the followups with:

    followup_id
    incident_id
    report_date
    due_date
    memo
    fatal

    Then when you have an insert, you can generate the due_date according to the report_date as dateadd("d", iif(fatal = -1, 3, 7),report_date).

    This will also come in EXTREMELY handy when you need to reference all followups for a given incident.

    Edit- had to change the dateadd function to reflect fatal/nonfatal.
    Last edited by Teddy; 03-12-04 at 16:04.

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    extremely great idea teddy! thats the magic of relational dbs and by doin that, generating queries and reports later on down the road will be tremendously easy;

  9. #9
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11
    Aha! Thanks guys. I will give this a try and see what I come up with!

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by bruch04
    I want one "Due Date" field to show when this case is due, depending on where it is in the process.
    I forgot to address this one... my bad.

    This is the primary reason I suggested altering your schema, with the two-table schema I suggested, you can get this date by executing the following query:

    SELECT Incident.incident_id, MAX(FollowUp.due_date)
    FROM Incident INNER JOIN FollowUp ON Incident.incident_id=FollowUp.incident_id

    Or you could leave out the max and generate a whole history for a report.

  11. #11
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11
    Well, I'm afraid I didn't understand your suggestion, so I guess I took the easy way out and created a hidden field called "CalDate" to calculate which date to use (initial, follow-up1, follow-up 2 etc), and then I calculated the due date by using an Iif statement applied to the CalDate, to return a 3-day or 7-day due date. These are the calculations I used:

    CalDate:
    =IIf(IsNull([Fu8]),IIf(IsNull([Fu7]),IIf(IsNull([Fu6]),IIf(IsNull([Fu5]),IIf(IsNull([Fu4]),IIf(IsNull([Fu3]),IIf(IsNull([Fu2]),IIf(IsNull([Fu1]),[Initial],[Fu1]),[Fu2]),[Fu3]),[Fu4]),[Fu5]),[Fu6]),[Fu7]),[Fu8])

    And for DueDate:
    =IIf([Fatal]=True,DateAdd("d",3,[CalDate]),DateAdd("d",7,[CalDate]))

    Thanks very very much for taking the time to respond. I'm sure what you suggested would work much better than what I came up with, if only I were experienced enough to implement it....

    Someday!

  12. #12
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Originally posted by bruch04
    Well, I'm afraid I didn't understand your suggestion, so I guess I took the easy way out and created a hidden field called "CalDate" to calculate which date to use (initial, follow-up1, follow-up 2 etc), and then I calculated the due date by using an Iif statement applied to the CalDate, to return a 3-day or 7-day due date. These are the calculations I used:

    CalDate:
    =IIf(IsNull([Fu8]),IIf(IsNull([Fu7]),IIf(IsNull([Fu6]),IIf(IsNull([Fu5]),IIf(IsNull([Fu4]),IIf(IsNull([Fu3]),IIf(IsNull([Fu2]),IIf(IsNull([Fu1]),[Initial],[Fu1]),[Fu2]),[Fu3]),[Fu4]),[Fu5]),[Fu6]),[Fu7]),[Fu8])

    And for DueDate:
    =IIf([Fatal]=True,DateAdd("d",3,[CalDate]),DateAdd("d",7,[CalDate]))

    Thanks very very much for taking the time to respond. I'm sure what you suggested would work much better than what I came up with, if only I were experienced enough to implement it....

    Someday!

    Would you be able to send me sample of your DB ? I'm working with similar database for the adverse event i would like to see how you implement this date function.
    Skharva

  13. #13
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11
    Sure...send me an email so I can reply to it with a file attachment. I can't upload the file onto the forum because the extension isn't accepted.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by bruch04
    ...crazy talk...
    Woah man.. you're seriously setting yourself up for some nasty work later on.

    If you want, IM me at XxteddysuxxX and I'll walk you through setting up the two-table schema. You should really take the time to learn how to do this, it's not nearly as daunting as it first seems and it will save you LOADS of hassle and headache later.

  15. #15
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by bruch04
    Sure...send me an email so I can reply to it with a file attachment. I can't upload the file onto the forum because the extension isn't accepted.
    You can send me at

    Skharva@yahoo.com


    Thanks
    Skharva

Posting Permissions

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