Back again, thirsting for knowlege. :) http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.
I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.
My Question now, is How do I figure in weekends? And Possibly Holidays.
i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.
I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.
Thank you
17  4001   ADezii 8,834
Recognized Expert Expert
Back again, thirsting for knowlege. :)
http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.
I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.
My Question now, is How do I figure in weekends? And Possibly Holidays.
i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.
I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.
Thank you
- 'Unless you modify the firstdayofweek Parameter to this Function:
-
'To see if a Date falls on a Saturday or Sunday:
-
If Weekday([YourDate]) = 7 Or If Weekday([YourDate]) = 1 Then
-
'the Date is on a Saturday (7) or Sunday (1)
-
'process code here
-
End If
-
-
'Holidays would have to be pre-determined and filtered out.
NeoPa 32,561
Recognized Expert Moderator MVP
Just to fill in with some more explanation :
Holidays is one of those often asked questions that seem to some, to be obviously very easy. Some need to be disabused! It is a very complicated area as it does not fit very well into the concepts of an RDBMS. There are threads in TSDN which go into this in more detail and do resolve the issue (mostly), but they are hard work and require extra maintenance.
Another similarly tough question that some feel should be very easy, is numbering lines returned from a query. Again, there are threads that explain how, but unless you have a hardy constitution, you don't want to go there.
- 'Unless you modify the firstdayofweek Parameter to this Function:
-
'To see if a Date falls on a Saturday or Sunday:
-
If Weekday([YourDate]) = 7 Or If Weekday([YourDate]) = 1 Then
-
'the Date is on a Saturday (7) or Sunday (1)
-
'process code here
-
End If
-
-
'Holidays would have to be pre-determined and filtered out.
So, where you have the "YourDate", I would switch with "LeaveDate" and "ReturnDate" Those being the fields that hold the dates in my app.
If Weekday([LeaveDate]) = 7 Or If Weekday([LeaveDate]) = 1Then
???? Week = 5 days? Then I would have to send 5 days plus any other days from the following week, to my "DaysGone" field. Something isnt making sense....could you by chance give me a little more detail?
I have spoke with the HR department, about the holidays and they said that, they could manually do everything else, but if I could get the weekends to subtract from my total number of days. Like every 7 days, then subtract 2 days or something. Either way, it makes my task a little less daunting knowing that I don't have to figure out the holidayz
Thx
NeoPa 32,561
Recognized Expert Moderator MVP
Have a look at ADezii's post again and see if you can work it out.
I'm going out now but will be back later. If you post that you still can't see why it's right then I will give a fuller explanation for you when I get back.
MMcCarthy 14,534
Recognized Expert Moderator MVP
So, where you have the "YourDate", I would switch with "LeaveDate" and "ReturnDate" Those being the fields that hold the dates in my app.
If Weekday([LeaveDate]) = 7 Or If Weekday([LeaveDate]) = 1Then
???? Week = 5 days? Then I would have to send 5 days plus any other days from the following week, to my "DaysGone" field. Something isnt making sense....could you by chance give me a little more detail?
This is simply a way of checking if a date is a saturday or a sunday. You would start at "LeaveDate" and check each date up to "ReturnDate" and add 1 for each date that wasn't a weekend day.
Mary
I thought DateDiff had a weekdays-only option. (Or am I thinking of DateAdd...)
NeoPa 32,561
Recognized Expert Moderator MVP
I've tried the 'Weekdays' option in Access2K (I forget which function - DateAdd() I think) and it's buggy. I ended up creating my own function to do it :(
I've tried the 'Weekdays' option in Access2K (I forget which function - DateAdd() I think) and it's buggy. I ended up creating my own function to do it :(
Oops!
Definitely another one to add to the tips and tricks.
NeoPa 32,561
Recognized Expert Moderator MVP
Oops!
Definitely another one to add to the tips and tricks.
If you like.
I'll get posting tomorrow probably.
Hi guys
I encounter the same pain at the moment in my MS Access. Can I know what is the VBA or Expression codes? Mine is similar; I have Date1 and Date2. I want to have a separate column which would count the difference of both dates (in terms of weekdays and if possible, exclude out certain days like public holidays, which would be added in manually). I’ve tried DateDiff but it didn’t work for me. At last, I used Date2-Date1 as temporary resort. Is there any other way to do this, at least counting only the weekdays?
Thanks.
Regards
Anthony
Back again, thirsting for knowlege. :)
http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.
I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.
My Question now, is How do I figure in weekends? And Possibly Holidays.
i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.
I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.
Thank you
- 'MoveWD moves datThis on by the intInc weekdays.
-
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
-
MoveWD = datThis
-
For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
-
MoveWD = MoveWD + Sgn(intInc)
-
Do While (Weekday(MoveWD) Mod 7) < 2
-
MoveWD = MoveWD + Sgn(intInc)
-
Loop
-
Next intInc
-
End Function
Ok, i think i have the process of this down in my head. But a few noob questions.
1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?
2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?
I understand what the code is doing, i guess im just missing how to integrate it into my project.
Thanks yet again :)
MMcCarthy 14,534
Recognized Expert Moderator MVP
1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?
This is just a function name. You use this name to call the function elsewhere
2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?
To call this function you would simply use ...
Me.DateFieldName = MoveWD(<Date Parameter>)
Date Parameter is whatever the variable name is that the calendar stores the date in.
 NeoPa 32,561
Recognized Expert Moderator MVP
1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?
MoveWD, as Mary says in her post, is the function itself.
2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?
Me.DateFieldName = MoveWD(<Date Parameter>)
Date Parameter is whatever the variable name is that the calendar stores the date in.
Generally you would create a new module and place it in there (unless you already had a general purpose module for such routines). Call it from your code.
Here are some more detailed steps to help : - From the Access window hit Alt-F11 to open and switch to the VBA window.
- Select Insert / Module.
- In the newly created module window paste in the code.
- You can now call it from just about anywhere in your project - SQL; Query; VBA Code; etc.
- The calling code is as Mary states, except that you also need to supply an intInc parameter. This says how many weekdays forwards (+) or backwards (-) you want to go.
What do you mean by "How many days you want to go forward (+)"?
The dates that I am working with are selected in two fields. One being the leave date and the other return date. The amount of days I want go forward is based on the difference between those two fields.
Is there another bit of code i need to stick in there to patch it all together?
NeoPa 32,561
Recognized Expert Moderator MVP
What do you mean by "How many days you want to go forward (+)"?
This is saying that you put in a positive number for moving the date onwards (forwards). Conversely, if you want a number of weekdays before a given date you use a negative number of days.
The dates that I am working with are selected in two fields. One being the leave date and the other return date. The amount of days I want go forward is based on the difference between those two fields.
Is there another bit of code i need to stick in there to patch it all together?
Are you trying to say that you want to find the number of weekdays between two dates rather than add (or subtract) weekdays to (from) a given date?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: StinkFinger |
last post by:
All,
Struggling with a query here.
table name : holidays
fields : holname (format varchar)
holdate (format date 2004-01-01)
The table is populated with all the holidays for the next 5 years. I am
trying to select only 1 of each holiday for the current year, AND if todays
date is past a holiday for the current year, then select the...
|
by: Peter Pfeiffer |
last post by:
I've written several scripts that have "while" blocks which increment a
date by one day if the date does not match one of a group of dates.
However, sometimes it apparently steps out out the while loop even
though my condition isn't met. Will work for a few loops then steps out
often.
Are there javascript "date" issues? I have also noticed...
|
by: SimonC |
last post by:
Help needed for a Javascript beginner.
As above in the subject... i need a javascript to run this, but not in
the form of a web-page. I want to calculate it between 2 fields in a
database that i have extracted into a report writer.
Look forward to hearing..
Cheers...
SimonC
|
by: PromisedOyster |
last post by:
Is there a way that I can get a resultset that contains unique dates in
a given date range without the need to have a temporary table and a
cursor?
perhaps something like:
declare @start_date as datetime
declare @end_date as datetime
set @start_date as '1/1/2005'
set @end_date as '1/1/2006'
|
by: Dixie |
last post by:
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is dd/mm/yyyy, I have found
that the dates I put in my holidays table are reversed into American dates.
So, the wrong holiday dates are subtracted...
| | |
by: MLH |
last post by:
With a table of holidays and A97's date fn's - how best to count
weekends and holidays between two dates? My holiday table
has 4 fields. I will be adding records to it each year as info
becomes known.
What approach would you advise to tally up the number
of holidays, saturdays and sundays between any 2 dates
in the range of my records in...
|
by: arbpen |
last post by:
I'm sure there's a simple way to do this, but I can't seem to find it:
If today is after February 1st and before February 15th then
do valentines
elseif today is after March 1st and before March 17th then
do stpatty's day
elseif today is April 1st then
do April Fool
elseif today is after April 15th and before May 5th then
do CincodeMayo
|
by: Dan2kx |
last post by:
Hello
i have a holiday table with staff ID's and single entry dates (for holidays skipping weekends)
I need a crosstab to show the dates as the column, and the staff id as the row, and count the holidays respective to each person, so it will show a "1" or be empty, i want to be able to specify the range that the dates show (ie month by...
|
by: Scholar81 |
last post by:
Hello,
I am a novice Access developer and my boss asked me to build a database and I said yes. Now I realized the bite is way too big and I'm trying not to choke ;-)
And what's worse, he does not want me to use VBA (thank god, one less thing for me to tear my hair out about). So I am struggling to figure out how to do this:
Count the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
