# Thread: Calculate Next Due Date

1. Registered User
Join Date
Jul 2011
Posts
13

## Unanswered: Calculate Next Due Date

I'm trying to calculate the Next Due Date for Training.

I have a table "Frequency" with the fields populated as: 1, 2, 3, 4, 5, 6

Next table is "Frequency Period": Month, Quarter, Year

These tables are used in another table as two combo boxes in order to show training frequency. (1 Year, 6 Month, 5 Year, 3 Year, etc.) Based on the values in these fields for each record I want to calculate the Next Due Date based on the Start Date of the training.

The Start Date and Next Due Date are in another table/form that is used for Scheduling Training. How do I combine the data results in the two fields (Frequency and Frequency Period) and calculate the next due date?

Thanks!

2. Moderator
Join Date
Mar 2009
Posts
5,442
You could use:
Code:
```If FrequencyPeriod = "Month" Then
Offset = Frequency
ElseIf FrequencyPeriod = "Quarter" Then
Offset = Frequency * 4
Elseif FrequencyPeriod = "Year" Then
Offset = Frequency * 12
End If

3. Registered User
Join Date
Jul 2011
Posts
13
how do i use this in the field "Next Due Date"

4. Moderator
Join Date
Mar 2009
Posts
5,442
If it's on a form, you can use:
Code:
`Me.("Next Due Date").Value = NextDueDate`
Or:
Code:
`Me![Next Due Date].Value = NextDueDate`

5. Registered User
Join Date
Jul 2011
Posts
13
Yes, the NextDueDate field is on a form but i need to use this field on reports too: do i need to create a field using the first code you sent

Code:
---------
If FrequencyPeriod = "Month" Then
Offset = Frequency
ElseIf FrequencyPeriod = "Quarter" Then
Offset = Frequency * 4
Elseif FrequencyPeriod = "Year" Then
Offset = Frequency * 12
End If
---------

Then in the NextDueDate field use this new code?
Code:
---------
Me.("Next Due Date").Value = NextDueDate
---------
Or:

Code:
---------
Me![Next Due Date].Value = NextDueDate
---------

6. Moderator
Join Date
Mar 2009
Posts
5,442
If you intend to use the computation in more than one place, you should create a Public Function that could be called from everywhere in the database. In an independant module (i.e. in a module that is not linked to a form or to a query):
Code:
```Option Compare Database
Option Explicit

Public Function NextDueDate(ByVal Period As String, ByVal Frequency As Long, ByVal StartDate As Date) As Date

Dim Offset as Long

If Period = "Month" Then
Offset = Frequency
ElseIf Period = "Quarter" Then
Offset = Frequency * 4
ElseIf Period = "Year" Then
Offset = Frequency * 12
End If

End Function```
In the form you can then use:
Code:
`Me![Next Due Date].Value = NextDueDate(Me.ComboPeriod.Value, Me.ComboFrequency.Value, Me.StartDate.Value)`
For a report it can be used in a similar way, but you can also use it in a query that will act as the data source for the Report (public functions can be used in queries).

#### Posting Permissions

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