Working days info (Access 97 Win2k)

Topic

New Reply

Hello again

I’m trying to work out the actual working days between any two dates. I’ve found two useful posts, 187790 and 85675, either one of which looks like it will solve my problem. The only question I have is where do I put the code?

I’d like to be able to use the result on a form and a report, to allow the user to view the information and then print it out if they want to. Would attaching the code to a buttons ‘on click’ property along with the code to open a form solve my problem? If so how do I get the result from the code into either a text box or label?

I think I have a few ideas, but not really sure if what I want to do is possible and at the minute I can’t get it to work.

Thanks for the help.

Ian

Reply | Quote

Replies

Me.txtResult.Value = WeekdaysMinusHolidays(Me.FirstOfMonthDate.Value, Me.LastOfMonthDate.Value)

I use this on a form load event.
Presuming you have the workdays function as a stand alone module.
txtResult is an unbound text on my form.
WeekdaysMinusHolidays is the module (The declaration text not module saved name.)
Me.FirstOfMonthDate.Value = date field on same form
Me.LastOfMonthDate.Value = date field on same form

Again I have the above two fields already populated.

Hope this gets you started.

Reply | Quote

Hans

As usual you are a real star, the ability to put this into a query was where I’d have been going next. I’d found the posts on the Access site by following the link to the ‘simple’ one that is on this forum.

Dave

Thanks, the information answers the one question I had left after Hans’ answer (I can run the ‘independant’ code from a form event).

This place is keeping me sane

Thanks again

Ian

Reply | Quote

Hope these answers solve your quest.

About the sane bit, I’m not sure.
I’ve been going insane for quite a while now.
The more I learn, the more possibilities I see, the more insane I go.

I think Hans will back me up on that.

Have fun.

Reply | Quote

Dave, why ask me to state the obvious?

Don’t worry, be happy

Reply | Quote

Because you’ve pulled me from the brink as I’ve been dangling on the edge so many times.

Reply | Quote

Hans / Dave

Thanks, I’m ‘back from the brink’ as it were, tried the code and had a problem with the sample posted by Hans, it did not like the ‘&lt’ bit at all (Access 97??), but once I’d checked out the other samples that were available from the links I figured out a way to make the thing work, but not why it wouldn’t, what does the ‘&lt’ do in the code, I can’t really find any refernce to it in the material I have, other than & as a way of setting values (no suggestion as to what value ‘lt’ is if that’s the answer).

I now have a sample query and a sample form that will produce the number of working days between datein and dateout. I’m ignoring the holiday bit as the database is for our Sales, and international customers don’t all have the same public holidays as the UK. If I start to get ‘over confident’ I’ll replace the current code with the set that allows for the use of a holiday table and put Christmas and Boxing Day in there….

As the Do … Loop is very poorly covered in ‘Access 97 programming for dummies’, what book should I get next? (Dummies is about my current level of ability though)

I’m off to celebrate with a stiff coffee, thanks again.

Ian

Reply | Quote

Hi Ian,

The problem you mention has bothered others too; it is not in the code itself but in the browser. In my code, I had a “less than” symbol. Sometimes, the browser will display the HTML code for “less than” instead of the symbol itself. This code is & lt (without a space in between). If you replace this by the “less than” symbol, the code should work.

BTW, Do…Loop is explained in the online help for Access 97; you’ll find several examples there (look up Do…Loop or Loops in the help index).

Reply | Quote

Here’s my module for Holiday-WorkDays and the table for holidays, (if you want them).

I’ve converted to A97.

The other problem you’ll have to check the attachment.
This seems to be a browser problem.

If I’m on the right trak, change them as with attached.
The text in the Attached is how my browser see’ it in Outlook Express.
Don’t know why it happens, but it does.

Reply | Quote

Sorry heres the module

Reply | Quote

Hans

Once I’d taken the time to tully understand what was happening in the code I replaced the ‘<' with a less than sign, presto it worked. In the long term it's better than just copying the code and only partially understanding what it does.

The Access on line help, is this on the Microsoft site? or are you referring to the help file off the menu in Access. If the latter, then I've read this and it makes great sense if you want to do something very simple, maybe I'm looking at the wrong part of the problem with my views of Do …. Loops and the Dummies book, my problem may be with what can be done while inside the actual loop, think I need to get a function reference from somewhere….. ://ambles off with steam coming out of ears//:

Dave

Thanks for the module, I'll have a look through it, but the code from the other Access site helped me figure out what was happening and once I'd corrected a few typos (I typed it in myself in order to try and get the methodology to sink in) it ran fine the way you told me to set it up.

Reply | Quote

Create a standard module (activate the Modules tab of the database window and click the New button on the right hand side). Enter or paste the function you want to use there. You will find a simple function that only omits weekend days here and a more complicated function that uses a list of holidays here (both on the Access Web).

If you prefer, you can copy this function and paste it into a module instead:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

WholeWeeks = DateDiff(“w”, BegDate, EndDate)
DateCnt = DateAdd(“ww”, WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If WeekDay(DateCnt) 1 And WeekDay(DateCnt) 7 Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd(“d”, 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

This function counts the number of days between two dates excluding Saturdays and Sundays. The ending date isn’t counted, so the number of working days between a Monday and the next Monday is 5. If you want to count the ending date, replace Do While DateCnt < EndDate by Do While DateCnt <= EndDate.

Let's say you have two text boxes on a form, txtDateFrom and txtDateThru, and you want to display the number of working days between them.
Put an unbound text box on the form and set its Control Source to

=Work_Days([txtDateFrom],[txtDateThru])

You can also use the function in a query. Say you have a table with fields DateFrom and DateThru. In a query based on this table, create a calculated field DaysBetween as follows:

DaysBetween: Work_Days([DateFrom],[DateThru])

Reply | Quote