Working days query

[joolsUK0575]

Hi Folks

I need to calculate the number of days between two dates excluding the weekend (I am not bothered about bank holidays etc).

I am trying to find out if applications made by people have been registered within 3 days.

The initial date that the application has been date stamped is in a column called Date_r and the actual date that the application has been verified is in a column called Date_valid

A while ago I found something that worked on this forum but I've gone and lost it!

HELP!

Thanks for any and all help

Jools

 

[DJN]

This any good?

http://www.mvps.org/access/datetime/date0006.htm

 

[joolsUK0575]

Looks good. But how do I use it?

 

[DJN]

Use it in a query.

SELECT tblWorkDays.StartDate, tblWorkDays.EndDate, workingdays([StartDate],[EndDate]) AS NoOfDays
FROM tblWorkDays;


The function is in a seperate field. Copy and paste this into the query grid in SQL view and change to design view to see it work. You will of course have to change the table and field names to suit.

 

[joolsUK0575]

Thanks!

BUT. I STILL am at a loss to get this work.

I tried to create a query and change all appropriate names, nothing

What I have done now is create a view on SQL Server where I draw in the two date fields I need. Connected this view in to Access then just created a query based on the view. Within the view I put alias' in for the names of the two fields to match the original code provided on the forum.

It ain't working

Thanks for the help so far

Jools

 

[Mile-O]

Have you put the code in a module within your database so that your query knows where to pass the parameters to?

 

[joolsUK0575]

Hi there

Yes. I have copied and pasted the code in to a module within my database

 

[Mile-O]

A module attached to a form or a standalone module? It should be the latter.

 

[joolsUK0575]

I went to modules created a new module and pasted in the code from the link supplied earlier in the post.

What I am doing wrong?

I even did a make table query and created a new table called tblWorkDays with the two column names StartDate and EndDate

Still no joy

 

[joolsUK0575]

oh. just to add to my ongoing headache

When I try to run the query it says "Undefined function 'workingdays' in expression"

 

[DJN]

OK, lets try to get this to work. I will go through it step by step, so bear with me if I repeat something you have already done.

1. In the database window, click on Modules.
2. Copy and paste the function into the module.
3. Save the module and name it. fWorkingDays
4. To test that it works create a new table, tblWorkingDays with two fields,StartDate EndDate
5. Enter dates into both fields. e.g. 01/04/2005 and 17/04/2005
6. Create a new query, qryWorkingDays
7. Drag the fields from the table onto the query grid.
8. In the third column enter the following. NoOfDays: workingdays([StartDate],[EndDate])
9. Run the query and you should see three columns StartDate EndDate NoOfDays
10 StartDate and EndDate should show the dates 01/04/2005 and 17/04/2005 and NoOfDays should show 10

Hope this helps

David

 

[Mile-O]

An example in action...

 

[joolsUK0575]

Folks.

You are true genius!

Thank you

Jools

 

[Spidey]

Multiple Dates

If I have multiple Dates that I want to track, would I just keep repeating the module with the different date fields in it. I track a multiple step process at work with about 8 different in and out dates. Would I put all the code in a different module or create a module for each step?

Thanks!

 

[Kempes]

How you get round the Null Values as this causes the query to fall over.

 

[Kempes]

I meant HOW do you get round Null Values????

 

[EMP]

I meant HOW do you get round Null Values????

Take a look at this thread. Jon K posted an expression to count the number of working days between two dates. http://www.access-programmers.co.uk/forums/showthread.php?t=87474


I have removed the part about holidays from Jon K's expression and used it in the query in SJ McAbney's database. I have also added some records for testing.

Jon K's expression tests for Null dates using IsNull(). When the query is run, it returns:

[b]ExampleID	StartDate	EndDate		Days	Modified from 
							Jon K's Expression[/b]
1		20/02/2005	30/04/2005	  50	   50
2		17/01/2005	25/01/2005	   7	    7
3		12/06/2005	12/06/2005	   0	    0
4		12/06/2005	13/06/2005	   1	    1
5		13/06/2005	13/06/2005	   1	    1
6		13/06/2005	14/06/2005	   2	    2
7		13/06/2005			#Error
8				14/06/2005	#Error

Note: Jon's expression returns the same number of working days as when the line
StartDate = StartDate + 1 in the WorkingDays() function is commented out
(that is, when StartDate is counted as the first day.)

^

 

[Veron]

EMP,

A very simple, effective expression!

Just wonder if we can modify Jon K's expression to return the number of working days, not counting StartDate as the first day, too.

Veron

 

[EMP]

EMP,

A very simple, effective expression!

Just wonder if we can modify Jon K's expression to return the number of working days, not counting StartDate as the first day, too.

Veron

Veron,

Yes, we can.

The database now contains two functions, one with the line StartDate = StartDate + 1 commented out.

I have also added an Expression in the query that doesn't count StartDate as the first day. The query now returns:

[b]ID   StartDate	EndDate	    VBA		Exp	    VBA		Exp
			    StartDate	StartDate   StartDate	StartDate
			    AsFirstDay	AsFirstDay  NotAs	NotAs
			    			    FirstDay	FirstDay[/b]
1   20/02/2005	30/04/2005	50	    50		50	    50
2   17/01/2005	25/01/2005	 7	     7		 6	     6
3   12/06/2005	12/06/2005	 0	     0		 0	     0
4   12/06/2005	13/06/2005	 1	     1		 1	     1
5   13/06/2005	13/06/2005	 1	     1		 0	     0
6   13/06/2005	14/06/2005	 2	     2		 1	     1
7   13/06/2005		     #Error		     #Error
8		14/06/2005   #Error		     #Error

^

 

[Kempes]

Can somebody post an access 97 version please if possible?