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?
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.
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,