Future date

[NNNNN]

HI I have a form with 3 text boxes

text1 I input date
text2 I input a number (number of days)
Text3
I want this to be text1+dateadd("d",text2)
BUT I do not wnt to include weekends

so if text1 was 06/12/2002
text2 was 3
then i would like text3 to be 11/12/2002

Plese help
Thank you

 

[JeremyNYC]

fire up groups.google.com and search through the archives of comp.databases.ms-access. You'll find some code there dealing with work days, business days, or holidays.

I got some code from there a while ago and it works quite well. I don't have it with me, or I'd send it to you now.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[MichaelRed]

Public Function basSchedDate(DtIn As Date, NDays As Integer) As Date

    'Michael Red 8/24/02;    Tek-Tips thread181-343737
    '?basSchedDate(#8/23/2002#, 30)
    '10/4/02

    'With the usual disclaimers re this being a DEMO.  Error trapping and
    'decisions re the return Values for such are STRICTLY the responsability
    'of the user.

    'In an actual production process, the 'Holidate' array
    'herein should be placed in a seperate RECORDSET, and an administrative
    'Process (e.g. a form for others to use to set/edit the dates) should
    'be provided.
    
    'If the Holidate within the Bounds of the Holidates is not checked,
    '(and Suitable Rtetrn Codes issued) you MAY be obtaining incorrect results.
    'The 'BUG' willdificult to spot, as the dates will still show up as
    '"week days", but the interval could be off by however many "holidays"
    'SHOULD have been in the interval

    Dim Holidate(23) As Date
    Dim CurDate As Date
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim HoliFlag As Boolean
    Dim MyWkDay As Integer

    'HoliDate                     'HoliName
    Holidate(0) = #1/1/2002#      'New Year's Day
    Holidate(1) = #1/17/2002#     'Martin Luther King Day
    Holidate(2) = #2/2/2002#      'Groundhog Day
    Holidate(3) = #2/12/2002#     'Lincon's Birthday
    Holidate(4) = #2/14/2002#     'Valentine's Day
    Holidate(5) = #2/21/2002#     'President's Day
    Holidate(6) = #2/22/2002#     'Washington's Birthday
    Holidate(7) = #3/8/2002#      'Ash Wednesday
    Holidate(8) = #3/17/2002#     'St. Patrick's Day
    Holidate(9) = #4/1/2002#      'April Fool's Day
    Holidate(10) = #4/20/2002#     'Passover
    Holidate(11) = #4/21/2002#     'Good Friday
    Holidate(12) = #5/5/2002#      'Cinco de Mayo
    Holidate(13) = #5/14/2002#     'Mother's Day
    Holidate(14) = #6/11/2002#     'Pentecost
    Holidate(15) = #6/18/2002#     'Father's Day
    Holidate(16) = #7/4/2002#      'Independence Day
    Holidate(17) = #8/21/2002#     'My Birthday
    Holidate(18) = #9/4/2002#      'Labor Day
    Holidate(19) = #10/31/2002#    'Halloween
    Holidate(20) = #11/11/2002#    'Vetran's Day
    Holidate(21) = #11/23/2002#    'Thanksgiving
    Holidate(22) = #12/25/2002#    'Christmas
    Holidate(23) = #12/31/2002#    'New Year's Eve

    Idx = 0
    CurDate = DtIn

    If (CurDate < Holidate(0)) Then
        'Users need to implement an Err Rtn Code here
        Exit Function
    End If

    While Idx < NDays
        MyWkDay = Weekday(CurDate)

        If (MyWkDay = vbSunday Or MyWkDay = vbSaturday) Then

            HoliFlag = True

         Else

            Jdx = 0
            While Jdx <= UBound(Holidate)

                If (Holidate(Jdx) = CurDate) Then
                    'Holiday.  Don't Count
                    HoliFlag = True
                    GoTo HolidayFlg
                End If
                Jdx = Jdx + 1
            Wend
HolidayFlg:
        End If

        If (HoliFlag = False) Then
            Idx = Idx + 1
        End If

        If (CurDate < Holidate(UBound(Holidate))) Then
            'Users need to implement an Err Rtn Code here
            Exit Function
        End If
        CurDate = DateAdd(&quot;d&quot;, 1, CurDate)
        HoliFlag = False

    Wend

    basSchedDate = CurDate

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

OK, I found the code that I use. I've not had problems with it. It relies on a table to store the holiday dates, which is nice, because then you can give the user an interface to modify that data as needed. I got this from Arvin Meyer. I'm not sure if he posted it to cdma or if I got it from his web site. He's a monster developer. Here it is:

Function AddBusinessDays(datStart As Date, ByVal intDayAdd As Integer)
On Error GoTo Error
'Adds the proper Business day skipping holidays and weekends
'Copyright Arvin Meyer 05/26/98
Dim gDB As Database
Dim rst As Recordset
Dim strSql As String

Set gDB = CurrentDb
Set rst = gDB.OpenRecordset("SELECT [HolDate] FROM tblHoliday", dbOpenSnapshot)

Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolDate] = #" & datStart & "#"
If WeekDay(datStart) <> vbSunday And WeekDay(datStart) <> vbSaturday Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

AddBusinessDays = datStart

rst.Close
Set rst = Nothing
Exit Function
Error:
ErrorTrap Err.Number, Err.Description, &quot;AddBusinessDays&quot;
End Function
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NNNNN]

I’m not getting on very well here I’m ashamed to say

I tried
unbound text box named DtIn I input date
unbound text box named NDays I input a number
unbound text box control source basSchedDate()

the result I get is #Name?

Likewise I have tried
I tried
unbound text box named datStart I input date
unbound text box named intDayAdd I input a number
unbound text box control source basSchedDate()

the result I get is #Name?

(I also used holiday table when the form contained the second function).

What am I doing wrong please
I really appreciate your help
thanks again

 

[NNNNN]

AAAAAAAAAAAAAAAH!

No sooner had I posted my lst post and I got it to work


thank you AGAIN

 

[JeremyNYC]

Did you have a problem when you used AddBusinessDays? You'll have to comment out my custom error handler (ErrorTrap Err.Number, Err.Description, &quot;AddBusinessDays&quot, but other than that, as long as you have the table, it should work. Oh, you might want to declare the variables like this:
Dim gDB As DAO.Database
Dim rst As DAO.Recordset

I'd be really curious, though, to hear about any problems you ran into using it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NNNNN]

Thanks Jeremy
I got it to work but I did have to comment out the error

I just used it on a blank database with only the holiday table.

I will try it out later with my database and let you know

Cheers!

What's the DAO for ?
it seem to work OK without

The problems I had were mainly syntax and ignorance on my part. such s giving my textbox the sourse as AddBusinessDays() but leaving the brackets empty
and not understanding the code

I know very little VBA but am inspired to learn


Thanks again

 

[JeremyNYC]

In the last two versions of Access, ADO is the default, not DAO, and you have to make a reference to it and be explicit in your declarations. In A97 there's no need.

There's no better way to learn than by doing, and it looks like you are. Keep at it. It's great stuff.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.