Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Unanswered: Future Date - update

    I am seeking help with how I might achieve the following in order to update a date field:

    If I have three (3) fields in a table named tblCPR

    1. RiskRating , this has a lookup to select EXTREME, HIGH, MEDIUM, LOW
    2. LastContact , this is a date
    3. NextContact , this is a date

    I have created a form automatically from the table which is called frmCPR

    I would like to be able to enter a LastContact (date) on the form, then after putting in that date, If I also select RiskRating = EXTREME from the drop down I would like the NextContact date field to be populated with
    a date which is = to 30 days ahead of the date entered in LastContact. So this will tell me I have to follow up this EXTREME contact in 30 days from my last contact.

    Then it would follow that if I enter a LastContact (date) with a RiskRating = HIGH I would like the NextContact future (date) to populate the NextContact date field with a date 90 days from the date entered in LastContact.

    Then if LastContact date entered with a riskrating = MEDIUM I would like Next Contact future date to populate the NextContact date field with a date 180 days from the date entered in LastContact.

    Finally if LastContact entered date has a risk rating of LOW I would like the NextCOntact (date) to populate the NextContact date field with a date which is 365 days from the date entered in LastContact.


    Thanks, Timmeh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally, I would work out the next contact at run time rather than store but assuming you've run through the various pros and cons and third normal form issues then....
    Add a column to your lookup table that includes the risk ratings.
    This column should store the appropriate number of days for each risk.
    Include this in your SQL statement populating the combo
    In the after update event of the combo, retrieve the number of days and write to the textbox something like:
    Code:
    Me.myTextBox = DATEADD("d", myRiskNumber, Me.myOtherTextBox)
    EDIT - corrected DATEADD syntax
    Last edited by pootle flump; 10-23-08 at 05:35.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest you don't store the next followup date as a value, derive it.

    first off I'd suggest you draw your risk ratings from a table, add a column to the table to indicate the next followupdate

    eg
    RiskRates
    Code string (5)?
    Description string (50)
    FollowUpDays integer
    make a RI rekationship between RiskRates and you appointment table
    Id suggest you use say -1 to say do not follow up, or say 36500 [100 years in the future should be far enough to be not an issue]

    you then have 2 issues, how to display this value, and how to display this value when the risk changes

    there are various methods
    in a report Id suggest you use a query
    select my, column, list, dateadd('d',followupdays,dateoflastappointment) as NextAppointment from mytable
    left join RiskRates on RiskRates.Code = mytable.RiskCode

    in your form you have to set the next appointment when you display a record
    AND whenever you change the risk code
    so in the forms on current event
    mycontrolname.value = dateadd('d',cint(dlookup ("Last_Name","RiskRates","Code=" & chr$(34) & myriskratecode & chr$(34)) ) , dateoflastappointment)

    in the listbox's on click event do the same thing, or better yet push the code above into a function and call it from the function. if you prefer you can be a little more gracefull in a function and not retunr any date say if its greater than 1 year... so you don't have to show dates that are 100 years ahead, you can handle do not follow up dates better.
    you can add in some logic to make sure the followup date is a working day.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm a little disturbed we agree so thoroughly Mark so I will restore order to our world and pick a fight
    Quote Originally Posted by healdem
    Id suggest you use say -1 to say do not follow up, or say 36500 [100 years in the future should be far enough to be not an issue]
    Why so scared of NULL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    'cos my new bessie mates Fabian & SQLUSA have enlightened me that NULLS are the spawn of the devil and should be avoided at all costs.

    as we speak Im genuflecting and crossing myself as rapidly as possible to ward off any evil that may have entered the world through my use of the 'N' word
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    [sarcasm]I am persuaded - I was not going to respond to that but then realised that was the old, unenlightened me.

    Response:
    -1[/sarcasm]

    EDIT - edited after interference by the Self Righteous.....
    Last edited by pootle flump; 10-23-08 at 07:36.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^

    I certainly try to avoid them, save using lots of NZ functions

    TheWorld = NZ(TheWorld, "")
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Guys, please use [sarcasm] tags, include a or at the very least Mr Winky should be the footnote on the post!
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049


    [sarcasm]Yes, I will do as you request oh wise and glorious leader :bow:[/sarcasm]



    ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Much better
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Guys, please use [sarcasm] tags, include a or at the very least Mr Winky should be the footnote on the post!
    Sorry I thought this was a US site, where sarcasm & Irony are not understood. So I'm guessing those tags will not work.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by healdem
    Id suggest you don't store the next followup date as a value, derive it.

    first off I'd suggest you draw your risk ratings from a table, add a column to the table to indicate the next followupdate

    eg
    RiskRates
    Code string (5)?
    Description string (50)
    FollowUpDays integer
    make a RI rekationship between RiskRates and you appointment table
    Id suggest you use say -1 to say do not follow up, or say 36500 [100 years in the future should be far enough to be not an issue]

    you then have 2 issues, how to display this value, and how to display this value when the risk changes

    there are various methods
    in a report Id suggest you use a query
    select my, column, list, dateadd('d',followupdays,dateoflastappointment) as NextAppointment from mytable
    left join RiskRates on RiskRates.Code = mytable.RiskCode

    in your form you have to set the next appointment when you display a record
    AND whenever you change the risk code
    so in the forms on current event
    mycontrolname.value = dateadd('d',cint(dlookup ("Last_Name","RiskRates","Code=" & chr$(34) & myriskratecode & chr$(34)) ) , dateoflastappointment)

    in the listbox's on click event do the same thing, or better yet push the code above into a function and call it from the function. if you prefer you can be a little more gracefull in a function and not retunr any date say if its greater than 1 year... so you don't have to show dates that are 100 years ahead, you can handle do not follow up dates better.
    you can add in some logic to make sure the followup date is a working day.
    It would be fine for me if I didn't have to store the "NextContact" date in a form. I am looking at this reply like a stunned mullet, I am in awe of the knowledge you guys have on access but am a complete dunderhead myself.

    If the question I posted has an answer that can be achieved solely in a report based on the RiskRating level and last contact date, can you please advise on that, or is the answer already here?

    Timmeh

Posting Permissions

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