Solved Show an end date on a form from entered start date and interval

[taball0829]

On this form, I would expect the user to enter a date in the "First day..." and choose month, quarter, or year. I would like to show the resultant "Last day..." on this form AND on another form along with a text "is the last day of the "& Interval.

I am thinking I need a Case attached to OnChange in both the "First day..." and "Interval," as I can't expect the user to necessarily ever go to "Last day..." to use GotFocus. I use a button on this form to go to the next form, but would rather display the last day before they click on the button, so I do not want the calculation to be OnClick for the button.

Can I Case on the ID of the Interval listbox?

It is safe to presume I know very little about VBA!

 

[theDBguy]

Hi. You can use an IIf() statement for the Interval argument. Give it a try and let us know how it goes.

 

[taball0829]

I put the following in the After Update of both the "First day..." and "Interval" unbound text boxes:
=IIf([balance_interval]="Mo",[last_day_of_interval]=DateAdd("m",1,[first_day_of_interval]),
IIf([balance_interval]="Qtr",[last_day_of_interval]=DateAdd("q",1,[first_day_of_interval]),
[last_day_of_interval]=DateAdd("yyyy",1,[first_day_of_interval])))

Nothing happened to the "Last day..."

I am going to try putting in Lost Focus.

 

[taball0829]

Oops! I had put them in Lost Focus. I have changed focus. Nothing.

 

[theDBguy]

I put the following in the After Update of both the "First day..." and "Interval" unbound text boxes:
=IIf([balance_interval]="Mo",[last_day_of_interval]=DateAdd("m",1,[first_day_of_interval]),
IIf([balance_interval]="Qtr",[last_day_of_interval]=DateAdd("q",1,[first_day_of_interval]),
[last_day_of_interval]=DateAdd("yyyy",1,[first_day_of_interval])))

Nothing happened to the "Last day..."

I am going to try putting in Lost Focus.

Try putting it in the Control Source of the Lastday Textbox to see if it works.

 

[arnelgp]

you can create a Function in a Module:

Public Function EndOf(ByVal freq As Integer, dte As Variant) As Variant
' arnelgp
' note:
'
' freq valid values:
'       1 = month
'       2 = quarter
'       3 = year
'
    Dim arrEofQtr As Variant
    Dim mo As Integer
    arrEofQtr = Array(3, 6, 9, 12)
    EndOf = Null
    If IsDate(dte) = False Then
        Exit Function
    End If
    Select Case freq
        Case Is = 1
            mo = Month(dte) + 1
        Case Is = 2
            mo = arrEofQtr((Month(dte) \ 4)) + 1
        Case Is = 3
            mo = 13
    End Select
    EndOf = DateSerial(Year(dte), mo, 0)
End Function

then on the AfterUpdate of Firstday textbox and Interval textbox add code:

private sub txtFirstday_AfterUpdate()
If Me.lstInterval.ListIndex > -1 Then
Me!txtLastday = EndOf(Me.lstInterval, Me!txtFirstday)
End If
end sub


private sub lstInterval_AfterUpdate()
If IsDate(Me!txtFirstday) Then
Me!txtLastday = EndOf(Me.lstInterval, Me!txtFirstday)
End If
end sub

see this demo:

 

[taball0829]

Thank you both for your input!!

I was having my midnight snack when it dawned on me why theDBGuy's plan was not working. It sounded to me like the correct answer. The If statement should have done what I wanted. I realized that the If was being confused (It always added a year, no matter what I chose: month, quarter, or year.) because I had both the ID and the Interval in the list box. I believe the If was looking at the ID rather than the Interval, and 1, 2, or 3 would never be true for Mo, Qtr, or Yr. I changed the list box to only show Mo, Qtr and Yr, and it is working perfectly.

Someone please mark this one Solved!

 

[Gasman]

Someone please mark this one Solved!

You can do that yourself in Thread Tools.

 

[taball0829]

Well, there is something wrong! For some strange reason, when the If calculates to March, it shows an end date of 3/28. Really odd. I think there was another month that did not calculate correctly, I think it was July, showing as 7/30, but that seems like ages ago.

Because I probably need to use this elsewhere, I thought I would give arnelgp's function a try. I see Month mod 4 in various threads. It works for months 1 - 6, but 7, 10 and 11 do not calculate correctly. Seven mod four plus one equals two, not three. Ten or eleven mod four plus one equals three, not four.

I changed two lines in the above function: The array, arrEofQtr = Array(4, 7, 10, 13) and the Quarter case, mo = arrEofQtr(DatePart("q", (dte)) - 1)

It seems I should not need to have the array one month greater, and the "-1" at the end, but I am pretty sure I was getting the end of the month of the previous quarter, for instance, the first quarter was yielding 12/31. As I said, that seems like ages ago, and it was only within the last 20 to 36 hours.

 

[arnelgp]

here i change the function.

 

[taball0829]

Elegant! I think one could change the array to accommodate fiscal years that are not January - December.