Using NetWorkDays in Access?

Is there a way for Access to recognize holidays in a function? I found the DateDiffW function that counts just weekdays, but I also need it to count holidays as well, within the same function. Here is the coding that I have so far, and it's working well. What I'm using it for is a "on hold" "off hold" calculation, that will be pulled into measurables. Any help would be appreciated!

	Public Function DateDiffW(BegDate, EndDate)
    Const SUNDAY = 1
    Const SATURDAY = 7
    Dim NumWeeks As Integer
    If BegDate > EndDate Then
        DateDiffW = 0
        Select Case Weekday(BegDate)
            Case SUNDAY: BegDate = BegDate + 1
            Case SATURDAY: BegDate = BegDate + 2
        End Select
        Select Case Weekday(EndDate)
            Case SUNDAY: EndDate = EndDate - 2
            Case SATURDAY: EndDate = EndDate - 1
        End Select
        NumWeeks = DateDiff("ww", BegDate, EndDate)
        DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
    End If

End Function

Also, is there a way to tell it if it sees a null value, not to give it an error? I have set this expression set up in a query

Expr2: IIf(IsNull([OnHoldREQ]),DateDiffW([Requisition Rec'd],[Submitted to Manager]),DateDiffW([Requisition rec'd],[Submitted to Manager])-DateDiffW([OnHoldREQ],[OffHoldREQ]))

When I run this, I get an error in my coding, highlighting this line:

	NumWeeks = DateDiff("ww", BegDate, EndDate)

Any help would be appreciated!!

Post your answer or comment

comments powered by Disqus

I have a module that references UBOUND. It is snaging on this. Is it possible to use this in Access?

If so do i need to reference a reference?




I would like to use formulas in Access 2000 the same way I use them in Excel. Is this possible? If so, how?? I'm at a loss. I need to perform calculations of different fields. For example, in Field3 I'd like to have the answer for: Field1 * (Field2 + Field4).

How do I do this. I am VERY NEW to this and I can use all the help and explicit instructions I can get. Thank you!



I have a form in access which contains a 6 x 4 table and some instructions (text) on loading of products.

I would like to create an email based on the template of this form.

How do you create tables in emails using VBA in access?



Found this on another site as to how to use NETWORKDAYS in Access.

There is a NetWorkDays function available to Excel worksheets but not directly available in Microsoft Access. You may have noticed that this function is listed in Microsoft Access help. This
is because we included the Excel help topics in case users were using the Excel spreadsheet components within Data Access Pages in Access 2000.

In any case, the NetWorkDays function is exposed in the Microsoft Office WebComponents function library (MSOWCF.Dll), and it is possible to reference this library from Microsoft Access and call this function indirectly by using your own function.

In order to use this particular function, follow these steps:

1. Insert a new module into your Microsoft Access database.

2. On the Tools menu, click References.

3. Scroll down through the list, and check the checkbox next to "Microsoft Office Web Components Function Library." If it is not in the list, then click the Browse button, and locate MSOWCF.Dll
which should be in your C:Program FilesMicrosoft OfficeOffice folder.

4. Click OK to close the References dialog box.

5. Insert the following code into the module you created in step 1.

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer

Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NETWORKDAYS(startDate, endDate)
Set objFunction = Nothing
End Function

This code allows you to indirectly call the NetWorkDays function from within Microsoft Access. You can call the GetNetWorkDays function, pass it the starting and ending dates, and then return the value from the NetWorkdays function.

If you want to call the function from a control on a Microsoft Access form or report, you would use the following syntax on the control's ControlSource property:

ControlSource: =GetNetWorkDays(#12/1/1999#, #12/17/1999#)

Of course, this example is using hard coded dates. You will need to replace the dates in the expression above with the dates you wish to calculate, or a reference to a field in your table which
contains them.

For instance, ControlSource: =GetNetWorkDays([StartDate], [EndDate])


I have installed a 60-trial of Office 2007 to see if I can resolve a problem I currently have with Acc2000 :

I currently use the "outputto" command in the Report_Page event of the report to output an RTF file, which is then converted to a PDF and then e-mailed to customers.

i.e. DoCmd.OutputTo acOutputReport, "Quote2", acFormatRTF, FPath

I have experienced formatting problems with this method (that are OK if previewed in Access) in that large gaps appears in the report data when the report is longer than 3 pages.

After installing Office 2007, I downloaded and installed the "SaveAsPDFAndXPS.exe" add-in.

I have tried to use the outputto method as follows :

DoCmd.OutputTo acOutputReport, "Quote2", acFormatPDF, FPath

Where FPath is the full path to a network share.
(Nothing else has changed other than the output format)

I was hoping to cut out the middle part of the process and go straight to a PDF output, which can then be e-mailed, however, even though Access tells me it is outputting the report, there is nothing in the network folder when I go to find it!!
I have tried outputting the report to a local directory, but get the same (non)result.

Can anyone tell me what I have missed / needs to be added to get this to work???

Ok here goes.....
I have a 2d array that is holding the proper information. The array is poulated in access. I need to use excel's cahrting capability to build a chart based on the info...
How do I pass the array into excel so I can use it as the base of my chart? There seems to be plenty of info on sending whole queries and tables, but what about variable values.

Any help would be great... thanks.


Hello all,

I believe this is an actual tough one, rather than just something I am being thick about...

I would like to use the results of a ping in Access. For example, I ping, if it is available, and therefore replying successfully, I would like Access to report the address as active, otherwise, the ping would time out, and Access should report it as inactive.

I have found an article on the web about it (HERE) but it is all about using it in VB6 - but I have no idea how to convert this into VBA.

Does anyone have any ideas? Or any other way to determine if a PC on the network is active or not?

Thank you very much indeed!

I found a module which is able to read and write the BLOBS in Access, but I am having problem using that module on my database, so kindly request help on that, and I hope this time I get the answer
See the attachment

I am having a problem using old code in Access Pro 2007.

This is the code I am using in Access 2003 without any problems but when I try this code in Access Pro 2007. I receive an error message.

Function Macro1()
On Error GoTo Macro1_Err

DoCmd.TransferSpreadsheet acExport, 8, "Payment Audit_1", "c:testingAudit.xls", False, ""
DoCmd.TransferSpreadsheet acExport, 8, "Vending Audit_2", "c:testingAudit.xls", False, ""
DoCmd.TransferSpreadsheet acExport, 8, "Coolers Audit_3", "c:testingAudit.xls", False, ""

Exit Function

MsgBox Error$
Resume Macro1_Exit

End Function

Can someone help? Thanks in advance.

Can someone confirm that Access 2000 does/doesn't support the use of .AddItem when populating the value list of a combo box. I do not have that version of Access on my pc.

It seems that when the following line is used in Access 2003 it works fine, however when run in an Access 2000 version Access says Method ro Data member not found

	Me.CboPeriod.AddItem "(All)"


Is there any way to use charts/graphs in Access-2007. I want to make some charts/graphs for my database as I used to do in Excel, but I am unable to do so. I have tried with Access help but unable to find the chart option.

Any help please......?

I would like to create some custom icons and use them in Access. Does anybody have an experience doing this that can start me down the right path? Thanks

Is it possible to use variable in an access querry. I wish to have the the year field (ex: d2002) to change automatically. The database I am using has about 50 different queries and changing the year on an annual basis, one by one, is a terrible waste of time.

If I'm barking up the wrong tree and someone has a much easier idea..... let me know

SELECT [Leaders List].name, [Leaders List].last, [Leaders List].employer, [Leaders List].d2002, [Leaders List].wpdiv
FROM [Leaders List]
GROUP BY [Leaders List].name, [Leaders List].last, [Leaders List].employer, [Leaders List].d2002, [Leaders List].wpdiv, [Leaders List].w_or_b
HAVING ((([Leaders List].d2002)>999) AND (([Leaders List].wpdiv)="44") AND (([Leaders List].w_or_b)="W"))
ORDER BY [Leaders List].employer;


I am putting together a very simple database that tracks work orders for a technical job.

Using Access 2003, pressing the 'Enter' key within the field, it works as I would expect and goes down a line. This way, I can keep my form looking nicely formatted without too much work.

In Access 2007, the default is to go to next record. I can change it to next field, or to stay put, but how do I get it to work as an 'Enter' key as it used to in Access 2003!?

Any advice welcome - I am an impatient learner!


Hi, im creating a query in Access using SQL. what i want is to have variables,


WHERE BETWEEN @from and @thru

It keeps telling me to declare the variable. can someone help me with the syntax on that please?



Our firm has recently switched to Access 2007 (i know that we are now in 2011 ), and running my old 2003 applications I have run into a small problem.
Using VBA I write items to listsbox using .AddItem, and eventually I write an empty line using '.AddItem ""'.
But in Access 2007 this is interpreted as a blank column.

How do I insert a blank line in a listbox using VBA in Access 2007?

I want to use VB to export a report using OutputTo command to excel and then format the spreadsheet using OLE in access. I currently use a macro in Excel, but I would like to skip that step. I'm not having any luck getting it to work. Anybody got any ideas or examples??

Just out of curiosity, does anyone use arrays in Access? If you do, can you tell me in what circumstances you use them?

Hi I'm just starting out using ADO in access but I can't get the code to compile in Access 97. The first part of the code is:

	Option Compare Database
Option Explicit

Private Sub Command0_Click()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim curdb As Database
Set curdb = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = "data source= " & curdb.Name
End With

What happens when I try to compile the module is I get the following message: "User-defined type not defined" and it highlights the line with "Dim cnn As New ADODB.Connection" in it
Does that mean I have not got ADO installed correctly

Can anyone help me out?


in excel there is " application.DisplayAlert = True "..
How can I use this in Access? I just dont want to get alert when my code running..
Please help me

I've been using recordsets in Access 97 and Access 2000 and now I have been trying to use them in Access 2003. If I use this code (which appears to me to be correct according to the online help in Access) then Access complains at runtime that ADODB.recordset is an undefined user-defined type.

Dim rst As ADODB.Recordset
Dim strCriteria As String

strCriteria = "idTransactionID = " & Me!numTransactionID

Set rst.Connection = CurrentProject.Connection
rst.CursorType = adopenDynamic
rst.locktype = adLockOptimistic
rst.Open "tblUnknownTransaction"

If I drop the "ADODB." bit then Access complains at runtime about the CursorType, locktype and Open properties

Have I missed something? Is there something wrong with my installation of Access 2003? or have Microsoft developed an aversion to the use of recordsets at 2003?

Hope someone can help
Thanks in anticipation

Hey guys,

next to VBA I'm also trying to pick up C#. A major part of this language is about object oriented programming and specifically classes.

I have a basic understanding how classes work. But I just fail to see where classes would be practical for me.

Maybe some examples of you guys using classes in Access would help me see the light?

Kind regards,
Hans B.

Does anybody know how to schedule in access like sql. Like a "job" in sql server?

I am currently attempting to use SQL in Access to alter a table by adding a new attribute and giving it a default value. Creating the attribute is fine but i keep getting an error when attempting to assign a default value. My code is as follows:

ADD Status Text DEFAULT 'Object is Currently In';

It keeps telling me that I have a Syntax error.

Any help would be greatly appreciated.


Not finding an answer? Try a Google search.