Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    NextDueDate = DateAdd("m", Offset, StartDate)
    Have a nice day!

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

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If it's on a form, you can use:
    Code:
    Me.("Next Due Date").Value = NextDueDate
    Or:
    Code:
    Me![Next Due Date].Value = NextDueDate
    Have a nice day!

  5. #5
    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
    NextDueDate = DateAdd("m", Offset, StartDate)
    ---------


    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. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
        NextDueDate = DateAdd("m", Offset, StartDate)
        
    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).
    Have a nice day!

Posting Permissions

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