Calculation (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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%5B/url%5D. 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.

Reply | Quote