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
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
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:
1. using Ubound in Access 2007 VBA
2. How do I use Formulas in Access 2000?
3. [Access] How to create tables in emails using VBA in access?
4. NETWORKDAYS for Access
5. Using "Outputto" in Access 2007 - PDF files
6. use array in access module to create excel chart
7. Using PING in Access
8. USING BLOBS in Access
9. Using code in Access Pro 2007
10. Using .AddItem in Access 2000
11. Using charts in Access
12. Can you use animated icons or titles in Access 2003?
13. Using variables in Access Queries
14. Use of 'Enter' key within a table in Access 2007
15. Using SQL in Access
16. Add blank line to listbox in Access 2007
17. Format Excel in Access
18. arrays in Access
19. ADO problems in Access 97
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 +
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!
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
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:
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
For instance, ControlSource: =GetNetWorkDays([StartDate], [EndDate])
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.
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 192.168.2.100, 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?
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
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.
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,
HAVING ((([Leaders List].d2002)>999) AND (([Leaders List].wpdiv)="44") AND (([Leaders List].w_or_b)="W"))
ORDER BY [Leaders List].employer;
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??
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
Option Compare Database
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
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & curdb.Name
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