due date

[awake2424]

I have a date field (original) in a form (All). I am trying to add 30 days to whatever is entered in the original field:

Private Sub Text225_AfterUpdate()
Me.Original + 30
End Sub

This doesn't seem right though. Thank you.

 

[pbaldy]

It would be in the after update event of original:

Me.Text225 = Me.Original + 30

 

[awake2424]

Me.Text225 = Me.Original + 30

Can this code be based on a work week (Monday though Friday) instead of 7 days?

Thank you.

 

[David R]

30 work days, irrespective of Holidays? That's 6 weeks, so just add 42 calendar days instead.

 

[awake2424]

The holidays need to be included in the calculation as well. Thanks.

 

[David R]

Where does Access get your holiday schedule from? What you're asking for is not a built-in function, but http://stackoverflow.com/questions/...ate-to-create-end-date-including-bank-holiday may be helpful.

 

[awake2424]

If I would like 30 days to be added if Text45 = Noonan Syndrome or 40 days to be added if it is Marfan Disorder, where do I put that in the code? There is a collectiondate field that has the startDate.

For example, if Noonan Syandrome is Text.45 and in collectiondate is 4/1/2013 then Text225 will be 5/3/2013 (30 days from the collectiondate excluding weekends)

Private Sub Text225_AfterUpdate()
 If Me.Text45 = "Noonan Syndrome" Or Me.Text45 = "Marfan Disorder" Then
 Public Function CountDays(StartDate As Date, NoOfDays As Integer) As Date
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim tmpStartDate As Date
Dim i As Integer

tmpNo = NoOfDays

tmpStartDate = StartDate

tmpDate = StartDate

i = 0

Do Until i = NoOfDays
    If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
        tmpNo = tmpNo + 1
    ElseIf DCount("*", "tblHoliday2014", "dtmDate = #" & tmpDate & "#") > 0 Then
        tmpNo = tmpNo + 1
    Else
        i = i + 1
    End If
    tmpDate = tmpDate + 1
Loop

CountDays = DateAdd("d", tmpNo, tmpStartDate)
End Function

Thank you.

 

[David R]

Doing it via code is going to make you crazy when you have to update/expand your list. Have a lookup table with your list of diseases and the associated collection period. You can use Dlookup or just a table join (depending on what you're doing/where you're doing it) to get the collection period, and updates are a matter of updating one or two fields in one table.

http://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx contains functions you can add to your database, to calculate your own weekday/workday intervals. There are others out there, that was just the first solid one Google gave me.

 

[awake2424]

I'm not sure I follow. I have a form with Order Date on it that the user enters.

Then there is a dropdown box on that form in which the user selects the test.

There is a text box (Due Date) that I would like to have auto-populate based on the test and Order Date. If the test ordered is "Noonan Syndrome" then 30 days is added to the Order Date. If the test ordered is "Marfan Syndrome" then 40 days is added to the Order Date. In order to get an accurate Due Date working days are used excluding holidays (which I have on a table). Thank you very much.

 

[David R]

My apologies, the MSDN link calculates workdays elapsed, not the end date for a given length of workdays. Try http://access.mvps.org/access/datetime/date0012.htm or http://www.utteraccess.com/forum/lofiversion/index.php/t1959936.html instead for Workdays math.

The bigger problem is that you are trying to determine CollectionDate in VBA! This is crazy-making, when you can just have a query with a calculated field CollectionDate: WorkdaysAdd(OrderDate,NoOfDays).

 

[awake2424]

I am using the code below and in the Due Date textbox:
=GetBusinessDay([«datStart»],[«intDayAdd»])

I get #NAME? in the field. Do I need to reference the Order Date (which is the starting date)? Thank you.

 Option Compare Database

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application provided author
'   is given credit. This code may not be distributed as part of a collection
'   without prior written permission. This header must remain intact.


Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHoliday2014", dbOpenSnapshot)

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

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

    GetBusinessDay = datStart

Exit_Here:
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    Exit Function
    
Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here
End Function

 

[pbaldy]

I'd expect:

=GetBusinessDay([YourFieldName],30)

where 30 was the number of days. That could also be a field name or form control, or something that returned an integer value.

 

[awake2424]

Could I do something like this:

=If Me.Text45 = "Test1" Then GetBusinessDay([YourFieldName],30) 
ElseIf Text45 = "Test2" Then GetBusinessDay([YourFieldName],4 0)

Basically, have different due dates based upon what test is ordered.

Thank you.

 

[pbaldy]

Sure, but you'd have to set the value of something to the result of the function (like a textbox). Alternatively to continue what you were doing:

=GetBusinessDay([YourFieldName],IIf(Text45 = "Test1", 30, 40))

 

[awake2424]

 =GetBusinessDay([Samplecollectiondate],IIf([Text45]="Noonan Sydrome",30,IIf([Text45]="Marfan Disorder",40)))

I get a #TYPE? in the Due Date.

Thank you.

 

[pbaldy]

The second IIf() serves no purpose that I can see. Can you post the db here?