Solved: Incrementing Value To Time/Date Field

[Mattster2020]

Afternoon All,

I have a field on a form called 'Lunch Start' and a field called 'Lunch Finish', the control source for both of these fields are date/time fields.

I want the user to be able to complete the lunch start field and have the lunhc finish field populated with the lunch start field plus 00:30.

Is there a way to do this?

[hansup]

I have a field on a form called 'Lunch Start' and a field called 'Lunch Finish', the control source for both of these fields are date/time fields.

I want the user to be able to complete the lunch start field and have the lunhc finish field populated with the lunch start field plus 00:30. You could use the DateAdd function in the AfterUpdate event of the Lunch Start field. In this example, my fields are named lunch_start and lunch_end:
Private Sub lunch_start_AfterUpdate()
If Not IsNull(Me.lunch_start) Then
Me.lunch_end = DateAdd("n", 30, Me.lunch_start)
End If
End Sub That would pre-load lunch_end based on lunch_start, but still give the user the option to change lunch_end in cases where lunch duration was other than 30 minutes.

On the other hand, if lunch duration must always be exactly 30 minutes, you may not even need to store a value for lunch_end ... just compute on-the-fly as needed.

Good luck,
Hans

[Mattster2020]

Thanks Hans,

Good stuff.

The code works. Although now how would I go about making sure the user can not enter a time less than 30 mintues in the lunch end field from the lunch start field. But be able to have greater than 30 minutes if required?

Regards,

Mattster

[Mattster2020]

Hi Hans,

Not to worry, I have solved the problem with the below code:

Private Sub Lunch_Start_AfterUpdate()
If Me.Lunch_Start < DateAdd("n", 30, Me.Lunch_Finish) Then
If Not IsNull(Me.Lunch_Finish) Then
Me.Lunch_Start = DateAdd("n", 30, Me.Lunch_Finish)
End If
End If
End Sub

[hansup]

Not to worry, I have solved the problem with the below code:Hi Mattster,

Your code included:
Me.Lunch_Start = DateAdd("n", 30, Me.Lunch_Finish) I think that line will set Lunch_Start to 30 minutes after Lunch_Finish.

As an alternative, you could set a Validation Rule on Lunch_Finish:
DateDiff("n",[Lunch_Start],[Lunch_Finish])>=30 (You can set the Validation Rule from the Data tab of the Lunch_Finish properties panel.)

Good luck,
Hans