 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Date Fields
Thanks to all the advice I have received but I seriously need more help (in more ways than one). The crux of the problem is that I need to insert a date into ClockStarts field and have access display another date into TargetDate field (plus 20 days but excluding Sat/Sun and holidays) If someone could give me the code to do this I would be forever in your debt. Thanks =Now() (if you want a date AND time value) =Date() (if you want date only)  SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > Thanks to all the advice I have received but I seriously need more > help (in more ways than one). [quoted text clipped - 5 lines] > If someone could give me the code to do this I would be forever in > your debt. Thanks (...) > The crux of the problem is that I need to insert a date into > ClockStarts field and have access display another date into TargetDate > field (plus 20 days but excluding Sat/Sun and holidays) > > If someone could give me the code to do this I would be forever in > your debt. Thanks If I understood well, You need to calculate TargetDate, which is equals =ClockStarts + 20 days (excluding Sat/Sun and holidays) If so then write: Public Function dTarget(dStart As Date, lDelay As Long) As Date Dim i As Long, Holydays Holydays = Array(#12/25/2007#, #12/26/2007#, #1/1/2008#) '... Do While i < lDelay dStart = dStart + 1 If Join(Holydays, ";") Like "*" & CStr(dStart) & "*" Then 'Holyday, we skip it ElseIf Weekday(dStart, vbMonday) > 5 Then 'Sat/Sun Else i = i + 1 'Working day End If Loop dTarget = dStart End Function K.P. MVP, Poland www.access.vis.pl > (...) > [quoted text clipped - 26 lines] > > K.P. MVP, Polandwww.access.vis.pl Sorry - Bit I have tried and tried but can not get it to work. Thanks anyway > If Join(Holydays, ";") Like "*" & CStr(dStart) & "*" Then > 'Holyday, we skip it [quoted text clipped - 3 lines] > i = i + 1 'Working day > End If Do you realize that both #1/1/2008# and #11/1/2008# are Like "*1/1/2008*"? James A. Fortune MPAPoster@FortuneJames.com (...) >> If Join(Holydays, ";") Like "*" & CStr(dStart) & "*" Then >> 'Holyday, we skip it [quoted text clipped - 5 lines] > > Do you realize that both #1/1/2008# and #11/1/2008# are Like "*1/1/2008*"? You are right, thanks. Code has to be a bit more complicated;-) Should be: If ";" & Join(Holydays, ";") & ";" Like "*;" & CStr(dStart) & ";*" Then Instead of original line below: If Join(Holydays, ";") Like "*" & Int(dStart) & "*" Then K.P. (...) >> If Join(Holydays, ";") Like "*" & CStr(dStart) & "*" Then >> 'Holyday, we skip it [quoted text clipped - 5 lines] > > Do you realize that both #1/1/2008# and #11/1/2008# are Like "*1/1/2008*"? You are right, thanks. Should be: If ";" & Join(Holydays, ";") & ";" Like "*;" & CStr(dStart) & ";*" Then Instead of original line below: If Join(Holydays, ";") Like "*" & CStr(dStart) & "*" Then K.P. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.