Validation Rule in Table

  • Thread starter Thread starter Emma Aumack
  • Start date Start date
E

Emma Aumack

I am trying to add a validation rule in the "Completed" field of a table as
follows:
=[date_entered]
Click to expand...

and I'm getting the error:

Invalid SQL syntax - cannot use multiple columns in a column-level check
constraint.

If I can't do this how can I make sure that the user does not enter a
completed date before the entered date?

Thanks!!
 
Okay, so I found some code that Ofer provided in a previous post as follows:

Private Sub Competed_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me.Date_Entered) And Not IsNull(Me.Completed) Then
If Me.Date_Entered > Me.Completed Then
msgbox "Date Completed must be after Request Date"
Cancel = True 'wont let the user exit the form
End If
End If

End Sub


My problem now is that when the user goes to enter the date completed, and
it is the same date as date entered (which has a default value of Now()), I
believe access is seeing it as a lesser value.

So what I need to do is somehow get the date entered to be saved as the
straight date (no minutes or seconds included in the value), so that my
comparison will work.

Any ideas on how I can do this?


--
www.bardpv.com
Tempe, Arizona


Emma Aumack said:
I am trying to add a validation rule in the "Completed" field of a table as
follows:
=[date_entered]
Click to expand...

and I'm getting the error:

Invalid SQL syntax - cannot use multiple columns in a column-level check
constraint.

If I can't do this how can I make sure that the user does not enter a
completed date before the entered date?

Thanks!!
Click to expand...
 
Emma said:
Okay, so I found some code that Ofer provided in a previous post as follows:

Private Sub Competed_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me.Date_Entered) And Not IsNull(Me.Completed) Then
If Me.Date_Entered > Me.Completed Then
msgbox "Date Completed must be after Request Date"
Cancel = True 'wont let the user exit the form
End If
End If

End Sub


My problem now is that when the user goes to enter the date completed, and
it is the same date as date entered (which has a default value of Now()), I
believe access is seeing it as a lesser value.

So what I need to do is somehow get the date entered to be saved as the
straight date (no minutes or seconds included in the value), so that my
comparison will work.
Click to expand...


Change the defult value from Now() to Date() and use an
UPDATE query to remove the time part from existing records:

UPDATE thetable SET Date_Entered = DateValue(Date_Entered)

OR, probably better, change the BeforeUpdate code to:

If DateValue(Me.Date_Entered) > Me.Completed Then
 
Back
Top