Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Thanked 0 Times in 0 Posts

    calculated columns - datedif function?

    I just begun trying to use Access for the first time today so I apologize if this is ridiculously basic, but I have already spent all day watching tutorial videos and searching posts. I'm trying create a very simple table that will allow me to keep track of my time for a project. I want to just record the date, time started, time completed and then have a fourth column that calculates time spent on task from the started and completed columns. Is this possible? I can't get the DATEDIF function to work in the table so I tried it in the control source of the form and it just displays #NAME? in the textbox. I know I could probably get it in a query but I would like to have it as a field in the table if possible.

  2. #2
    Join Date
    Jun 2010
    Thanked 1,400 Times in 1,222 Posts
    From a database design perspective, your fourth column doesn't make much sense, since it is redundant (it is what is frequently called a derived column, which can create consistency issues and Access doesn't offer any mechanisms to ensure the correctness of the value. I would avoid it and whenever you need to display that, I would indeed use a query or a control with a calculated value in a form.

    For specific syntax details, maybe this will help:

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts
    Like Ruirib I have always taken the view that this calculated value should not be stored, just calculated when needed (usually in a query).
    But Access 2010 does have a a new data type of calculated for fields in a table (which I have never used). Presumably if you perform the calcuation there Access will guarantee to keep the calculation up to date.

    But the list of functions offered for the calculation is somewhat limited, and does not appear to contain DateDiff.

Posting Permissions

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