Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation (Access 2000)

    Greetings,
    I have the following fields: AppStatus (with combo boxes New, Redeterm etc.), DateRecieved and DateDue. If New is selected from AppStatus drop down, I want DateDue field to be automatically 10 business days from the date application was received (DateDue= AppRecieved + 9). Can anyone tell me how to achieve this calculation?
    I'm using Access 2K.

    Thanks in advance.
    DD

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Calculation (Access 2000)

    You could do something similar to the following:

    On the On Change or After Update event of the AppStatus, add the following code.

    If Me.AppStatus.Value = "New" Then
    Me.DateDue.value = Me.DateReceived.value + 10
    End If

    Refresh

    This assumes that the Date Received value is already entered. You might set the date recieved value to the current date when New is selected if the value is null so you wont get an error.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation (Access 2000)

    Hi DD, there is some confusion here - you start by mentioning a field DateRecieved, then later on, you mention AppRecieved.

    Can you be sure that DateRecieved or AppRecieved has been filled in when AppStatus is set to New? If not, you'll get into trouble trying to set DateDue to a date 10 days later.

    To add business days, you need a custom function; Access doesn't have built-in support for that. You can find such a function at http://www.mvps.org/access/datetime/date0012.htm. You can call it from the AfterUpdate event of the AppStatus combo box:

    Private Sub AppStatus_AfterUpdate()
    If AppStatus = "New" Then
    If Not IsNull(DateReceived) Then
    DateDue = dhAddWorkDaysA(9, DateReceived)
    End If
    End If
    End Sub

    This assumes that you have copied the functions from the link mentioned above to a standard module in your database; otherwise, you won't be able to use the dhAddWorkDaysA function.

    Replace DateReceived by the actual name of the "received" field in your database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •