Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    Unanswered: Calculating dates

    Hi I have created an access database that accepts two dates.

    1) From date

    2) To date

    I am trying to calculate the difference when informtion is entered into the both fields.

    I am using =DateDiff("d",[From date],[To date]) in the control source proprities of the text box where the number would be generated, this text box is from the entery table.This works to an extent, where the number of days is generated. However when I check the entry table in which the difference should be captured it is not there.

    What am I doing wrong?

    How do I get the difference generated to be stored in the entry table where the amount field is?

    One more thing how do I calculate without weekends being included in a date calculation?

    Thanks much for the help.

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    You will probably get several responses that advise against storing a calculated value. Unless you are trying to optimize a process that is very slow, I would suggest always calculating the value and using the calculated value for decision making.

    As far as showing only business days, I was going to suggest using w instead of d. According to the help it is supposed to be Weekdays, but mine is not working properly (as I expected it would). Mine is returning exactly what ww returns. If you get the same results it looks like you will have to write your own function to count business days (with or without holidays).

    I know I have seen code for doing this but I don't remember where.

  3. #3
    Join Date
    Jan 2007
    California, USA
    My DateDiff "w" does not work like it should either, but here is one way to calculate the work days:

    And yes, I would suggest that you NOT store a calculated value in a table. Anywhere you can reference the value from the table, you can reference a function and the two dates that are used to calculate that value. Much, much safer and easier in the long run.

    If you MUST store it, then you can not use a calculation as the Control Source, but you would use the name of the field in the table. Then, somewhere, like when you enter the date in either field, you would calculate this difference and use code to put it into the field. Such as, CalculatedDifference = MyDateDiffFunction(DateFrom, DateTo)
    HTH, and hope you don't store the calculated difference,

Posting Permissions

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