exclude holidays and weekends in access (1 Viewer)

Hello all,
-My goal is to count the number of days between two dates excluding holidays and weekends, then send those results to a new table each month.

-Here is my situation and problem. I’ve written my entire program in Access 2000 w/o having to write any code. So I really don’t know anything about using a function and will need some help trying to implement this. But I think I need to “some-how” run my date fields through a function I have found, and then “some-how” send the results to a new table.

-The function I have found is from this forum. When I create a module and paste the function into the VB Editor and use the Immediate Window, I get results like:

? 7/30/2002 9/16/2002
1.16550116550117E-04 2.80969030969031E-04
? 1/1/2001
4.99750124937531E-04

-I don’t know what this is or what to do with it or if it is even right. I created a table called tblHolidays. It has two fields called Holidays(text) & HoliDate(date/time). I have 9/4/2002 in there.

Here’s the function I’m trying to use:
“Calculating the workdays between Dates”
' Note that this function does account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays

End Function

-Here are the details in my access db:
Main table name = 10_Consumer
Main table fields = Alert_Date_Referral ie. Date: 7/30/2002
Sent_to_State_Fax ie. Date: 8/16/2002
Received_from_State ie. Date: 9/11/2002
Start_of_Care ie. Date: 9/16/2002
Full_Name ie. Name: Doe,John

-The math I need to do is as follows and I can currently do with a query in access:

DAYS_FOR_US_TO_SEND_TO_STATE:[Sent_to_State_Fax]-[Alert_Date_Referral]
DAYS_FOR_STATE_TO_COMPLETE:[Received_from_State]-[Sent_to_State_Fax]
DAYS_FOR_US_TO_START_CARE:[Start_of_Care]-[Received_from_State]
TOTAL_DAYS_FOR_US_TO_COMPLETE[Sent_to_State_Fax]-[Alert_Date_Referral])+([Start_of_Care]-[Received_from_State])
TOTAL_DAYS_FOR_PROCESS[Sent_to_State_Fax]-[Alert_Date_Referral])+([Received_from_State]-[Sent_to_State_Fax])+([Start_of_Care]-[Received_from_State])

-The answers I can get are:
DAYS_FOR_US_TO_SEND_TO_STATE: 17
DAYS_FOR_STATE_TO_COMPLETE: 26
DAYS_FOR_US_TO_START_CARE: 5
TOTAL_DAYS_FOR_US_TO_COMPLETE: 17+5=22
TOTAL_DAYS_FOR_PROCESS: 17+26+5=48

-But the answers I need exclude holidays and weekends (holiday on 9/4) and are:
DAYS_FOR_US_TO_SEND_TO_STATE: 13 (I do count 7/30 and don’t count 8/16)
DAYS_FOR_STATE_TO_COMPLETE: 17
DAYS_FOR_US_TO_START_CARE: 4
TOTAL_DAYS_FOR_US_TO_COMPLETE: 13+4=17
TOTAL_DAYS_FOR_PROCESS: 13+17+4=34

-I then need to create a table with these results including the consumer name each time I do this, which will be once a month.

-I hope this is what you will need to help me. Thank you in advance!

Brad Garrett

I assume the whole function that you have saved in a module is as follows:-
-----------------------------------
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"
' Note that this function does account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays

End Function
---------------------------------------

Try this query (it would be easier to directly type/paste in the SQL View of a new query):-

SELECT Name, Work_days([Alert_Date_Referral], [Sent_to_State_Fax]-1) AS DAYS_FOR_US_TO_SEND_TO_STATE,
Work_days([Sent_to_State_Fax], [Received_from_State]-1) AS DAYS_FOR_STATE_TO_COMPLETE,
[Start_of_Care] - [Received_from_State]-1 AS DAYS_FOR_US_TO_START_CARE,
DAYS_FOR_US_TO_SEND_TO_STATE + DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_US_TO_COMPLETE,
DAYS_FOR_US_TO_SEND_TO_STATE + DAYS_FOR_STATE_TO_COMPLETE + DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_PROCESS
INTO tblNewTable_1
FROM 10_Consumer;


When the query is run, a table [tblNewTable_1] will be created. The -1 in the query is to exclude from the counts the [Sent_to_State_Fax] day and [Received_from_State] day.

I've had to modify the code just a bit, but I got it to run.

SELECT Name, Weekday([Alert_Referral],[Sent_to_State_Fax]-1) AS DAYS_FOR_US_TO_SEND_TO_STATE, Weekday([Sent_to_State_Fax],[Received_from_State]-1) AS DAYS_FOR_STATE_TO_COMPLETE, [SP_Start_of_Care]-[Received_from_State]-1 AS DAYS_FOR_US_TO_START_CARE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_US_TO_COMPLETE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_STATE_TO_COMPLETE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_PROCESS INTO tblNewTable_1
FROM 10_Consumers;


I don't think it's is pulling dates from the 10_Consumers table, because it is prompting me for the info.

I just got this error:
"This expression is typed incorrectly, or is too complex to be evaluated. ... Try simplifying the expression by assigning parts of the expression to variables.

and

After entering the dates, I get a "data type missmatch" error.

Any Ideas?

Thanks,

I find that the function name, some field names, and the table name shown in your SQL statement are not the same as those posted in your first post.


I have attached a DB that I created from the data gathered from your first post. It also contains the function and SQL statement in my previous post.

Try running the query. It should give you the figures 13,17,4,17,34 that you needed.

You can then modify the attached DB to reflect the actual function name, field names and table name that you are using.

Hope it helps.


(Note: The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)

Thank you very much! This is working for me. This is rather taxing on the computer. It seems like the larger the date range the longer it takes to process, which makes sence.

If Null?

I need to turn all null values to 0. I think I should use
IIF(isNull([Alert_Referral]), 0, [Alert_Referral]
But I'm not sure where it goes. Can you help?

There's a specific function for changing null values: the Nz() function.

i.e.

Nz([Alert_Referral], 0)

In your query you would basically have it like this as the field value:

Alert_Referral: Nz([Alert_Referral], 0)

Thanks!

Module Issue

How can I modify the Work_Days module, so that if it comes to a null date, it will return 0?

fix to return zero at null date?

Can anyone change this module so that when it comes across a null date, it will return a zero?

Jon K said:

I assume the whole function that you have saved in a module is as follows:-
-----------------------------------
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"
' Note that this function does account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays

End Function
---------------------------------------

Click to expand...

Can someone help me with the Nz() function for the sample DB in this post.

Here is what the query looks like, where would I place the Alert_Referral: Nz([Alert_Referral], 0) line? I need to account for values.

thanks

SELECT [Name], Work_days([Alert_Date_Referral],[Sent_to_State_Fax]-1) AS DAYS_FOR_US_TO_SEND_TO_STATE, Work_days([Sent_to_State_Fax],[Received_from_State]-1) AS DAYS_FOR_STATE_TO_COMPLETE, [Start_of_Care]-[Received_from_State]-1 AS DAYS_FOR_US_TO_START_CARE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_US_TO_COMPLETE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_STATE_TO_COMPLETE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_PROCESS INTO tblNewTable_1
FROM 10_Consumer;

anyone?

vandido626 said:

Can someone help me with the Nz() function for the sample DB in this post.

Here is what the query looks like, where would I place the Alert_Referral: Nz([Alert_Referral], 0) line? I need to account for values.

thanks

SELECT [Name], Work_days([Alert_Date_Referral],[Sent_to_State_Fax]-1) AS DAYS_FOR_US_TO_SEND_TO_STATE, Work_days([Sent_to_State_Fax],[Received_from_State]-1) AS DAYS_FOR_STATE_TO_COMPLETE, [Start_of_Care]-[Received_from_State]-1 AS DAYS_FOR_US_TO_START_CARE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_US_TO_COMPLETE, DAYS_FOR_US_TO_SEND_TO_STATE+DAYS_FOR_STATE_TO_COMPLETE+DAYS_FOR_US_TO_START_CARE AS TOTAL_DAYS_FOR_PROCESS INTO tblNewTable_1
FROM 10_Consumer;

Click to expand...