Validation of Dates

[grinder332518]

My table is defined as follows :

Reference text Primary Key
StartDate date Primary Key
EndDate date can be null

I currently have a row with :

Reference = ABC,
StartDate = 1st Jan 2009, and
EndDate = 1st November 2009.

I want my Form to allow me to enter a 2nd row with
Reference = ABC, and
StartDate greater than or equal to the EndDate above.

If I enter an EndDate prior to this, then I want to display a meaningful message.

This should be the case for all References, not just ABC.

Should I do so as a Validation Rule in the table design
Or an ‘on enter’ event in the Form design ?

And what would the coding look like ?

Many thanks

[ChipR]

I think I would use the BeforeUpdate event of the form, and check the entered date against a

DLookup("StartDate", "myTable", "Reference = """ & Reference & """")

[NeoPa]

@grinder332518
I would assume you actually mean StartDate.

With that basic assumption, and that the controls on your form are named as the fields are in the table, then you want something like the following in your Form_BeforeUpdate() event procedure :

Dim datMax As Date
 
datMax = DMax("[EndDate]", _
              "[YourTable]", _
              "[Reference]='" & Me.Reference & "'")
If Me.StartDate < datMax Then
    Call MsgBox("Your failure message here")
    Cancel = True
    Exit Sub
End If

[grinder332518]

Thanks NeoPa.
As you gathered, I did mean StartDate.
Your suggestion works beautifully.... as far as it goes.
However, I simplified my example when posting.
The field “Ref” should actually be 2 separate fields : “Ref1” and “Ref2”.
I have tried to enhance your coding by altering the 3rd argument :

   "[Ref1]='" & Me.Ref1 & "'" , "[Ref2]='" & Me.Ref2 & "'")

but cannot get the syntax correct.
Can you assist again please ?
Many thanks.

[NeoPa]

You're nearly there. You have to use "AND" between the two items :

   "[Ref1]='" & Me.Ref1 & "' AND [Ref2]='" & Me.Ref2 & "'")

The syntax is identical to the WHERE clause in SQL except without the keyword WHERE, if that helps.

Although it's rarely necessary, I usually put parentheses around separate items thus :

    "([Ref1]='" & Me.Ref1 & "') AND " & _
    "([Ref2]='" & Me.Ref2 & "')")

[grinder332518]

Thanks NeoPa
I should have mentioned that Ref2 is defined as a number, not text.
Will this effect the coding ?
Also, when I create a new Ref1 / Ref2 combination, there is no existing EndDate for a previous row. Will the code still allow me to enter a StartDate ?
Many thanks for your continuing support.

[missinglinq]

Excuse me, but do you ever plan on giving us all the facts here? Posting a problem, and when it's answered, saying "Oh, but that's not all" and when that's answered saying "Oh, but that's not all" and repeating this four times is simply not acceptable!

State your problem, in full, when you originate your post!

Linq ;0)>

[NeoPa]

As Linq says, it is only considerate to give a bit more thought to your question before posting it. It may be tedious, but if not doing so causes those trying to help you to spend extra time, fundamentally unnecessarily, on your problems, then this should be seen in the context of asking a favour. We're happy to put in the time if it's for something you're struggling with, but less happy to spend our time simply to save you the effort of preparing your question properly.

I appreciate your first effort was an attempt to be quick and not unduly complicated, which is a good thing. I'd just request, much as Linq did, that you prepare a little more carefully to avoid unnecessary time wastage.

Moving on now to the question.
@grinder332518
Yes. Definitely.

The single-quotes will not be required for numeric literals. They are only required for strings. See Quotes (') and Double-Quotes (") - Where and When to use them & Literal DateTimes and Their Delimiters (#) for the full explanation.
@grinder332518
This is a scenario I hadn't coded for. Such a situation would crash the code as it now stands. Try instead, something like :

Dim datMax As Date
 
datMax = Nz(DMax("[EndDate]", _
                 "[YourTable]", _
                 "[Ref1]='" & Me.Ref1 & "' AND " & _
                 "[Ref2]=" & Me.Ref2), #1/1/1900#)
If Me.StartDate < datMax Then
    Call MsgBox("Your failure message here")
    Cancel = True
    Exit Sub
End If

[grinder332518]

many thanks for your help.
the code is working fine now.
I'll be more specific in future.