Field Validation (1 Viewer)

j4n3tt3

New member
Local time
Today, 02:56
Joined
Oct 5, 2017
Messages
2
I'm having difficulty and don't know what to do

I have a field called Date Out and a field called Date Returned.
Validation in Date Returned is [Date Returned]>[Date Out] but it's not working!!
Message is
Data Integrity rules have been changed, existing data may not be valid for new rules (well that's obvious) We click Yes and then
Invalid Syntax says cannot use multiple columns in a column level check constraint.
I understand what this is saying but I don't know what to do now to make sure that the date returned is after the date out.
Many thanks for any help
J:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:56
Joined
Sep 21, 2011
Messages
14,050
Do the validation on the form?
 

Minty

AWF VIP
Local time
Today, 09:56
Joined
Jul 26, 2013
Messages
10,355
I'm not sure that would work. Presumably if it hasn't been returned and the return date is null your validation rule would fail? Also if it's not yet booked out?

You need to allow for the null value. Something like

([Date Out] Is Null) OR
([Returned Date] Is Null) OR
([Returned Date] >= [Date Out])

And get rid of those spaces in the field names - causes so much extra typing and typos.
 

j4n3tt3

New member
Local time
Today, 02:56
Joined
Oct 5, 2017
Messages
2
Thanks - I'll check this out and yes, we're working on getting rid of the spaces :( . Next lesson is tomorrow now
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:56
Joined
Feb 19, 2002
Messages
42,981
Field level validation is very limited. You have a little more flexibility if you add table level validation in that you can reference other columns in the same table. However, the only functions you can use are functions known to SQL. No VBA or UDF functions are supported. Why? Because the column/table validation is performed by the database engine Jet or ACE and Access does not even need to be installed so VBA is not available.

As Gasman suggested, do the validation in the form.

Use the FORM's BeforeUpdate event. That way both columns should be present if they were entered so you can include presence in your tests.

Code:
If IsDate(Me.[Date Out]) Then
    If IsDate(Me.[Date Returned] Then
        If Me.[Date Returned] <= Date() Then
            If Me.[Date Returned] >= Me.[Date Out] Then
                'valid
            Else
                Msgbox "Date Returned must be >= Date Out.",vbOKOnly
                Me.[Date Returned].SetFocus
                Cancel = True
                Exit Sub
            End if
        Else
            Msgbox "Date Returned must be <= Today's Date.", vbOKOnly
            Me.[Date Returned].SetFocus
            Cancel = True
            Exist Sub
        End If
    End If
End If
 

Users who are viewing this thread

Top Bottom