DateDiff Returning Zero

[Paul]

Access 2007 Trial. I have to fields in the form footer, [Min Date] (=
Min([Date Worked]) and [Max Date] (=Max([Date Worked]) both work fine. When
I try to use DateDiff on them I zero returned. I have DateDiff("w", [Min
Date], [Max Date]) as I only want to count weekdays. Test data is populated
with data running from 01/11/2007 - 01/15/2007 and I'm expecting it to
return 3. Any ideas?

Thanks

Paul

 

[John Vinson]

Access 2007 Trial. I have to fields in the form footer, [Min Date] (=
Min([Date Worked]) and [Max Date] (=Max([Date Worked]) both work fine. When
I try to use DateDiff on them I zero returned. I have DateDiff("w", [Min
Date], [Max Date]) as I only want to count weekdays. Test data is populated
with data running from 01/11/2007 - 01/15/2007 and I'm expecting it to
return 3. Any ideas?

DateDiff knows NOTHING about workdays, and that's not what the "w"
does; in practice, "w" and "d" both return the integer number of days,
and I've never understood why the "w" is provided at all. You'll need
to use some VBA, and probably a table of holidays as well; you don't
really want to count *weekdays* but *workdays*, so if Monday is being
observed as a holiday (pausing to remember Rev. King...) you would
want to take it out of the count as well. There's sample code at

http://www.mvps.org/access/datetime/date0012.htm

John W. Vinson[MVP]

 

[Paul]

Great, thank you both. I'll give them both a try.

Paul