UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Calculating between dates excluding weekends and holidays, Office 2007    
 
   
Doug Steele
post Feb 4 2011, 06:46 AM
Post#21


UtterAccess VIP
Posts: 21,956
Joined: 8-January 07
From: St. Catharines, ON (Canada)


As well, the use of "[holidays]= # " & DateCnt & "# " in the DLookup statement means it won't work consistently for users whose Regional Settings are such that their Short Date format is dd/mm/yyyy.
Go to the top of the page
 
HiTechCoach
post Feb 4 2011, 12:29 PM
Post#22


UtterAccess VIP
Posts: 19,008
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Teo,
This might also be a bug in your code:
If Format(DateCnt, "ddd") <> "Sut" And
Go to the top of the page
 
teoteodoridis
post Feb 14 2011, 02:23 PM
Post#23



Posts: 11
Joined: 3-February 11



Gustav i guess the first point of your remarks might be true ( i suspected that from the first time i set up on this code); as it concerns the last part of your comments;i propose that someone could change the english letters "sat" for saturday or "sun" for sunday with those of his own lunguage ( i use that succesfully with the greek three initial letters for theese days).
Thanks anyway!
Go to the top of the page
 
teoteodoridis
post Feb 14 2011, 02:36 PM
Post#24



Posts: 11
Joined: 3-February 11



HiTechCoach,
meant "Sat" but I wrote "Sut" by mistake.
Note as well that someone has to change the date format of the holidays field on the form (put in the afterupdate event of the field) to that:
Me.holidays = Format(Me.holidays, "mm/dd/yyyy")
(In this way you will force the programe to take into consideration the onedigited (for the day) dates(holidays) like 5/mm/yyyy or 9/mm/yyyy) and exclude them from the count
with regards.
Go to the top of the page
 
teoteodoridis
post Feb 14 2011, 02:42 PM
Post#25



Posts: 11
Joined: 3-February 11



Doug Steel,
This can be simly avoided if someone canges the format from dd/mm/yyyy to mm/dd/yyyy !
Ogave a full answer to HiTechCoach!
Go to the top of the page
 
Doug Steele
post Feb 14 2011, 02:50 PM
Post#26


UtterAccess VIP
Posts: 21,956
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Access uses the date format set through Regional Settings. Are you suggesting telling the user that he/she must change his/her chosen settings in order to use your application? If you told me that, I'd quickly remove your application from my machine!
You may want to read what Allen Browne has in International Dates in Access or what I had in Access Answers: Access Developer Needs a Date.
HAs to your comment that the code needs to be changed to handle the user's language, look up the Weekday function. You'd replace
If Format(DateCnt, "ddd") <> "Sut" And _
Format(DateCnt, "ddd") <> "Sun"
with
If Weekday(DateCnt) <> vbSaturday And _
Weekday(DateCnt) <> vbSunday
Go to the top of the page
 
teoteodoridis
post Feb 14 2011, 03:00 PM
Post#27



Posts: 11
Joined: 3-February 11



Gustav ,
tested my code in a year(with 366 days lets say 2012) -i guess that means leap years-and it really worked !
I think the merit 1/365 (the difference betwwen a 365 days year and a 366 days year) is too little as a number to affect the combination for a short period of time
it would take o lot bigger period so as to affect the results.
You can also test that!
Thanks anyway!
With regads.
Go to the top of the page
 
teoteodoridis
post Feb 14 2011, 03:06 PM
Post#28



Posts: 11
Joined: 3-February 11



thanks,
never implied that someone should change regional settings but just to change only the format of the holidays field or any date field(on the relative form)
,putting the appropriate code (on the afterupdate event) this will not affect the regional settings at all!
For else you can format the datefields (using the reletive function:format()) to whatever you like adjusting the code to what is likable to your regional settings ,
though keeping it's basic philosophy!
Go to the top of the page
 
Doug Steele
post Feb 14 2011, 03:14 PM
Post#29


UtterAccess VIP
Posts: 21,956
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry, but that's not correct. Please read the articles I cited.
Go to the top of the page
 
Gustav
post Feb 14 2011, 05:14 PM
Post#30


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



> it would take o lot bigger period so as to affect the results.
Well, I never understood when you have the choice between something that works correct and something that may work correct, why some pick the least secure option.
Also, you have to realize that the format of dates are for display only or for building string expressions of date values for SQL and a few other situations where values are represented by strings. Normally dates should and can be handled as dates; if you have to fall back to strings like your "sat" or "sut", most often something is wrong or lacks optimizing.
/gustav
Go to the top of the page
 
namron
post Aug 31 2011, 03:12 PM
Post#31



Posts: 85
Joined: 31-August 11
From: Lancashire,UK


Thanks very much to Gustav for his code which is just what I was looking for. I've managed to include it in a query in my database and it works .... but I do have a problem which I could do with some help on.
ome of the end dates in my table are blank. When the query runs these produce a result of #Error. I'm then unable to filter or sort on that field in the query.
If the end date is not present I'd like these records to just produce a blank in the query result if possible. I've tried to come up with an IF statement but I'm a newbie on this and I'm not able to come up with the correct syntax.
Any help would be appreciated.
Thanks
Norman
Go to the top of the page
 
Gustav
post Aug 31 2011, 03:56 PM
Post#32


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



You could replace those empty end dates with the start date to obtain a zero count for these records:
ISO_WorkdayDiff([StartDate],Nz([EndDate],[StartDate]),True)
/gustav
Go to the top of the page
 
namron
post Sep 7 2011, 05:02 PM
Post#33



Posts: 85
Joined: 31-August 11
From: Lancashire,UK


Thanks very much Gustav.
This was previously working fine (apart from the problem mentioned). Unfortunately now I'm getting an error after calling the module in my query which says "The expression contains an ambiguous name. Verify that each name in the expression refers to a unique object."
I've copied your code again and only edited the table name and field name for my holidays table, then I've entered the following in the field row of the query design - Workdays: ISO_WorkdayDiff([ArrivalDate],Nz([ComplDate],[ArrivalDate]),True)
Could you help me resolve this error?
Thanks
Norman
Go to the top of the page
 
Gustav
post Sep 8 2011, 09:32 AM
Post#34


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



One of the used field names appear in more than one table, or you may have copied the function into more than one module.
gustav
Go to the top of the page
 
namron
post Sep 8 2011, 03:44 PM
Post#35



Posts: 85
Joined: 31-August 11
From: Lancashire,UK


Thanks Gustav, this works great now!
orman
Go to the top of the page
 
Gustav
post Sep 9 2011, 05:26 AM
Post#36


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



You are welcome!
gustav
Go to the top of the page
 
Barbll
post Feb 24 2012, 02:40 PM
Post#37



Posts: 36
Joined: 13-January 11
From: Ontario, Canada


I have two databases that I run that use this date difference code as well as the exclude holidays code for workday calculations. We're running Access 2002 and they've been working great.... until we upgraded to Office 2010.
How when the database is opened in Access 2010 we get error messages. I have since found out that two files (mscal.ocx and fpdtc.dll) were excluded from the latest version of Access. I think this is causing quite a few headaches for folks out there using calendars.
Onot an expert but would it be the VBA date difference and exclude holidays code that uses these mscal.ocx and fpdtc.dll files? Can this code be edited without too much trouble so that it would work with Office 2010 or would I need to look for new code?
Any help you can provide would be very much appreciated. Many thanks.
Go to the top of the page
 
Gustav
post Feb 24 2012, 06:07 PM
Post#38


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



No, this code is not at all related to the two files you mention, thus nothing to edit.
You'll have to look elsewhere for what troubles the missing files - a control and a library - cause and work around these.
gustav
Go to the top of the page
 
Barbll
post Feb 27 2012, 12:47 PM
Post#39



Posts: 36
Joined: 13-January 11
From: Ontario, Canada


Thanks for your reply Gustav. It saves me 'barking up the wrong tree' <
Go to the top of the page
 
Yorky_North
post Feb 28 2012, 05:25 AM
Post#40



Posts: 54
Joined: 4-May 11



if it does not have to be exact.
CODE
Function NumberOfWorkingDays(dteOne As Date, DteTwo As Date)
nbsp;   NumberOfWorkingDays = Int(DateDiff("ww", dteOne, DteTwo) * 5)
End Function
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 10:44 AM