Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2008
    Posts
    12

    Unanswered: Auto calculating date problems

    Hey all.

    I have a problem with auto calculation dates between two post.

    I have one post called 'Date of diagnosis' and one called 'Date of treatment', now i would like a post which automaticly fills out the number of days between the two dates.

    In Excel this would be pretty easy, its just A minus B, but I have difficulties doing this in Access.

    Can anyone help me out here or maybe walk me through creating such a post.

    Regards, Montanius

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You just need date diff:
    http://www.techonthenet.com/access/f...e/datediff.php

    Also - calculate this on the fly - don't store the value.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2008
    Posts
    12
    Hey, thx for the fast reply.

    I found the Datadif function aswell, but I am in doubt on how to tie it to the object. Do I post in BeforeUpdate or what?

    And how do I link the dates from the 2 post i enter? The example from ur link is with two specific dates?

    Again, thx a lot for ur help on this...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would create a function. Call the function in the afterupdate of the two relevant date text boxes. If they are both filled in, return:
    Code:
    DATEDIFF("d", Me.FirstTexbox, Me.SecondTextbox)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a new query based off the table in question, open SQL view and paste the following in there
    Code:
    SELECT [Date of diagnosis]
         , [Date of treatment]
         , DateDiff(dd, [Date of diagnosis], [Date of treatment]) As [difference]
    FROM   <insert table name>
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you are in T-SQL mode there George. "d" rather than dd.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    He musta forgot the sarcasm tags ^^
    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
    Second time today ST...







    ...stop making me laugh out loud!
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Awwww... but I like making people laugh
    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
    Oct 2008
    Posts
    12
    Quote Originally Posted by georgev
    Create a new query based off the table in question, open SQL view and paste the following in there
    Code:
    SELECT [Date of diagnosis]
         , [Date of treatment]
         , DateDiff(dd, [Date of diagnosis], [Date of treatment]) As [difference]
    FROM   <insert table name>
    When doing this it tells me that there is a syntax error 3131 in FROM clause

    This is what I put in there:

    SELECT [Date of Diagnosis], [Date of metastatic presentation], DateDiff(d,[Date of Diagnosis],[Date of metastatic presentation]) AS difference
    FROM Klinisk Kernedata UMM


    FROM Klinisk Kernedata UMM being the main table's name (Clinical coredata translated)

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check the syntax hint
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    FROM [Klinisk Kernedata UMM]
    The text you have entered is too short....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Oct 2008
    Posts
    12
    Thx guys.

    Ive now been able to create a query which calculates the correct number of days

    However, I would now like to link the data from that query into to original table...

    ... or on the same report via relationships, how do I do this? It wont make reports with both a table and a query based on that table...

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a new query, joining the two data sources, then base your report of the result
    George
    Home | Blog

  15. #15
    Join Date
    Oct 2008
    Posts
    12
    Wow, this is getting complicated...

    Maybe a bit too complicated for me, isnt there a way where u can create a function that does this within the same table or form, like an excel datasheet?

    If this is what pootle told me how to im gonna need more specifics, what do i enter in afterupdate, how do i create the function etc.

    Sorry for being a complete idiot at this, but I might be in over my head, im a med student

Posting Permissions

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