DateDiff Problem

[matthewnsarah07]

I am currently using access to collate staff holiday requests, at the moment they enter [Date From], [Date To] and [Total Days]

I have been trying to use the DateDiff function to auto fill the Total Days field but it does not seem to work, I am using this expression in the fields default value on the form

DateDiff ("y", [Date From], [Date To])

What am i missing?

 

[boblarson]

Actually, if you are going to be using total number of days (including weekends and holidays) then you shouldn't store that info. You can get it at any time with the date diff function.

Second, your formula should be using "d" instead of "y"

 

[missinglinq]

Unless the staff are taking very, very long holidays!

 

[boblarson]

Unless the staff are taking very, very long holidays!

funny Linq!

Actually, I think you could either just manually enter the number of days (if you want to exclude holidays and weekends) or you can go through and use that code that is in the repository to exclude them. But, doing it with a simple DateDiff is going to count holidays and weekends against them and you may not want that if you are keeping track of their vacation days used.

Just a thought.

 

[missinglinq]

You know, that's a very good point, Bob! People get so caught up in automating everything that many times I think they spend ten times the energy it would require to to something the old fashioned way! Unless we're talking about managing hundreds or thousands of vacation requests, entering the number of days manually is probably the best choice. It's fine to talk about having a table of holidays to include in your calculations, but on top of everything else, this table is not a one time thing, but has to be edited every year, and if this app were to be used corporate-wide over a multi-state area, could need to be modified top include local/state holidays!