If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to use a function in a query
I found a query while searching the newsgroups that I think will
accomplish exactly what I want. I will include the function after I finish this post. Anyway, the name of the function is WorkingDays2 and it's purpose is to count the number of working days from one date to another, taking in to consideration holidays, Saturdays, Sundays, etc. I have the issue where we work some Saturdays, but not all, so it is very hard to do. Anyway, I haven't ever used a function in Access before, so I am not sure what to do with it. I know that I have to define it as a PUBLIC function, and it goes in the Modules page. But, once I have it there, how do I use it in a query? If you need more info, please let me know. TIA, Tim Here is the Function: Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function |
#2
|
|||
|
|||
How to use a function in a query
On 24 Feb 2007 08:30:47 -0800, Zeunasc wrote:
I found a query while searching the newsgroups that I think will accomplish exactly what I want. I will include the function after I finish this post. Anyway, the name of the function is WorkingDays2 and it's purpose is to count the number of working days from one date to another, taking in to consideration holidays, Saturdays, Sundays, etc. I have the issue where we work some Saturdays, but not all, so it is very hard to do. Anyway, I haven't ever used a function in Access before, so I am not sure what to do with it. I know that I have to define it as a PUBLIC function, and it goes in the Modules page. But, once I have it there, how do I use it in a query? If you need more info, please let me know. TIA, Tim Here is the Function: Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function If you look at the first line of the function... Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer you will notice there are two arguments (separated by a comma within the parenthesis) that ask for specific date information., i.e. (StartDate As Date, EndDate As Date) That means when you call the function, you have to pass the 2 date values to the function for it to process. Add a new column to your query grid. DaysWorked:WorkingDays([StartDateField],[EndDateField]) Change [StartDateField] and [EndDateField] in the query to whatever the actual date field names are in your table. NOTE1: The above function requires a table of holiday dates to run properly. NOTE2: A function placed in a Module is Public, so there is no need to explicitly state it. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|