Date Fields

[Paul]

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

 

[Jeff Boyce]

=Now() (if you want a date AND time value)

=Date() (if you want date only)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Krzysztof Pozorek [MVP]]

(...)

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

 

[Paul]

(...)



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, Polandwww.access.vis.pl

Sorry - Bit I have tried and tried but can not get it to work. Thanks
anyway

 

[James A. Fortune]

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

Do you realize that both #1/1/2008# and #11/1/2008# are Like "*1/1/2008*"?

James A. Fortune
(e-mail address removed)

 

[Krzysztof Pozorek [MVP]]

(...)

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.

 

[Krzysztof Pozorek [MVP]]

(...)

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.