Ensure dates from two fields(DateFrom and Dateto) are correct and calculatetotaldays

[xwnoob]

Hi,
I have two textboxes which allow users to type in two dates for a certain period.(DateFrom and DateTo) How do i make sure the users do not put a date in DateTo that is earlier that DateFrom ? also how do i calculate the difference between the two dates?

[jzwp11]

You can put in a validation rule in for the DateTo control on your form like this:

[DateTo]>[DateFrom]

You will have to make sure your users entered the DateFrom first.

To determine the difference between two dates, you will want to use the datediff() function. This expression will find the number of days between the two date values

=DateDiff("d",[datefrom],[dateto])

[xwnoob]

You can put in a validation rule in for the DateTo control on your form like this:

[DateTo]>[DateFrom]

You will have to make sure your users entered the DateFrom first.

To determine the difference between two dates, you will want to use the datediff() function. This expression will find the number of days between the two date values

=DateDiff("d",[datefrom],[dateto])

Ok, so here is my code for the validation :

Private Sub AvailablePeriodEnd_BeforeUpdate(Cancel As Integer)
If [AvailablePeriodStart] > [AvailablePeriodEnd] Then
MsgBox ("You have entered a date wrongly")
Me!AvailablePeriodEnd = ""
End If
End Sub

However the code in red gives me an error everytime. How do i replace this code with a code that resets the value in the dateto field? I just want the field to be blank if the user enters wrong date.

[jzwp11]

You cannot reset the value while you are in the control from which the code was executed. You can, however, prevent the record from being entered if you put the code in the before update event of the form and since the code is not executing from the control, you can reset it

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [availableperiodstart] > [availableperiodend] Then
  MsgBox ("You have entered a date wrongly")
  Cancel = True
   Me.availableperiodend = Null
End If
End Sub

[xwnoob]

You cannot reset the value while you are in the control from which the code was executed. You can, however, prevent the record from being entered if you put the code in the before update event of the form and since the code is not executing from the control, you can reset it

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [availableperiodstart] > [availableperiodend] Then
  MsgBox ("You have entered a date wrongly")
  Cancel = True
   Me.availableperiodend = Null
End If
End Sub

Ok but can i use this code in conjunction with the code inside the control?


Also, how do i implement your code inside the form_beforeupdate event with another code that i have:


Private Sub Form_BeforeUpdate(Cancel As Integer)
'Ensure that users do not accidentally save records
If Not Me.NewRecord Then
If MsgBox("Do you want to save the updated records?", vbOKCancel, "Save?") = vbCancel Then
Cancel = True
Me.Undo
End If
End If
End Sub

How do i combine the codes

[jzwp11]

You will want to check the date before you check if they want to update the record because otherwise once you update the record it will be too late to check the date.