0

Excel's NETWORKDAYS function returns the net working days between two dates. Is there a way of replicating this when connecting to a Jet database via ADO?

Lunatik
  • 3,838
  • 6
  • 37
  • 52
  • what language are you programming in? There are certainly no Jet/ACE functions that are accessible via ADO to do this for you, but you can certainly recreate the function in whatever language you're programming in. That's where logic like that belongs, in any case. – David-W-Fenton Sep 17 '09 at 04:45
  • The query is coming from an Excel front-end, so VBA. – Lunatik Sep 17 '09 at 07:33
  • @David W. Fenton: "you can certainly recreate the function in whatever language you're programming in. That's where logic like that belongs" -- you think that every application that shares the same database should implement their own logic? "Golly gee whiz, do you suppose that might be a bad idea? Let’s just list some of the assumptions and situations required for this approach to work.1) This application and database code will remain the same forever...2) Nobody else will ever write new code against this database... – onedaywhen Sep 18 '09 at 10:07
  • "...3) If anyone does write new application code against this database, he or she will always get the [logic] correct... 4) If several programmers write new application code against this database, they will always get the [logic] correct and identical..." http://www.dbazine.com/ofinterest/oi-articles/celko25 – onedaywhen Sep 18 '09 at 10:08

2 Answers2

2

See:

Why should I consider using an auxiliary calendar table?

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

  • How many business days between x and y?

While the article includes SQL Server code, the concept is universal and the code can be ported to the Access Database Engine with ease.

Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

A bounty on my original question on SU finally teased an answer out of the community

https://superuser.com/questions/35716/equivalent-of-excels-networkdays-function-with-jet-ado

Community
  • 1
  • 1
Lunatik
  • 3,838
  • 6
  • 37
  • 52