Calculating Number of Work Dates From 2 Date Fields

[sara82]

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?

Any help will be appreciated, thanks

 

[RoyVidar]

Most of the working suggestions made here at Tek-Tips involves a bit of code and an extra table for additional holidays. MichaelRed's faq faq181-261 is very often mentioned, as it is good, and can be called from form controls, queries, other code...

If you're using 2000+ version, I'd recommend altering the declarations of recordset and database objects from

[tt] Dim dbs As Database
Dim rstHolidays As Recordset[/tt]

to

[tt] Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset[/tt]

Roy-Vidar

 

[sara82]

Thanks for the help reference. But I keep getting an error.

I created a module and pasted the code there

I have Access 2003 so I changed

Dim dbs As Database
Dim rstHolidays As Recordset

to

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

I created a table with the name tblHoidays and the Field Name of HoliDate.

I created a query and input
NoOfDays: DeltaDays([Date Received],[Data Approved]) as the field.

When I run the query I get an error. The code for the module opens up and it says:
Compile Error: User-defined type not defined.

How can I correct this?

 

[sara82]

Sorry forget to mention that the debugger highlights the following

dbs As DAO.Database and rstHolidays As DAO.Recordset

 

[PHV]

And which line of code is highlighted when in debug mode ?
Have you ticked the Microsoft DAO 3.6 Library in the menu Tools -> References ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886