Results 1 to 3 of 3

Thread: Date Query Help

  1. #1
    Join Date
    Feb 2006

    Exclamation Unanswered: Date Query Help

    I work in a hospital and need to set a form to display a date. The problem is that we have 6 different treatment types (Surgery, Chemo, Radiotherapy, Brachytherapy, Palliative care & Active monitoring) and i have an Access Database with 6 fields for their starting dates. These dates need to be kept seperate.
    The patient will only ever have one of these filled in as this is for their their first treatment.
    I am trying to display this information in a form with only one field for the six types. (Treatment Start Date)
    Can someone help with the query i need to write to show this.
    If the result is a date i would like that entered, if it is blank, then nothing.

    Many thanks

    Last edited by tonyhagger; 10-26-07 at 06:27. Reason: Added extra info

  2. #2
    Join Date
    Feb 2006
    I think i am getting a bit closer now... I am using the following expression.
    =IIf([DTT Date (Surgery)] Is Null," ",(Nz([DTT Date (Surgery)])+Nz([Waiting Time Adjustment (DTT)])+31)) Or ([DTT Date (Anti-Cancer Drug Regimen)] Is Null," ",(Nz([DTT Date (Anti-Cancer Drug Regimen)])+Nz([Waiting Time Adjustment (DTT)])+31)) Or ([DTT Date (Teletherapy)] Is Null," ",(Nz([DTT Date (Teletherapy)])+Nz([Waiting Time Adjustment (DTT)])+31)) Or ([DTT Date (Brachytherapy)] Is Null," ",(Nz([DTT Date (Brachytherapy)])+Nz([Waiting Time Adjustment (DTT)])+31)) Or ([DTT Date (Specialist Palliative Treatment Course)] Is Null," ",(Nz([DTT Date (Specialist Palliative Treatment Course)])+Nz([Waiting Time Adjustment (DTT)])+31)) Or ([DTT Date (Active Monitoring)] Is Null," ",(Nz([DTT Date (Active Monitoring)])+Nz([Waiting Time Adjustment (DTT)])+31))
    But it is giving me a date of 29/12/1899.

    Someone please help.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    why not 'just' extract all 6 dates and pull the appropriate value in the front end; instead of writing a fiendishly complex IIF query that will be a pig to maintain, test & debug).

    mind you the very fact that you are having these sort of problems suggests to me that you basic physical design is suspect. had you pushed the detail down to a sub table then you could extract the specific value you want by identifying the treatment type. Another advantage of this approach is if say the medics invent some whizzy new treatement, or relabel a treatment or otherwise dick about you can introduce a new treatment type without having to recode.

Posting Permissions

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