Synchronise Date Fields

[LoneAngel666]

Clueless Newb - needs help with Date fields

Hey all,

I'm constructing a database in Access for a project, and I'm stumped as to how to get two date fields to work with each other. The layout is as follows (you'll probably think this is simple) :

I have a "Agreement Starts" date, and an "Agreement Ends" date, and I need to figure out how to ensure that the date entered into the "Agreement Ends" field is always later than the date entered in "Agreement Starts". I've tried messing with the expression builder, but I find it really confusing.

Any help would be great,
Many thanks - Rob

 

[Mile-O]

This is the sort of thing you may find better to do on a form.

Are you currently trying to enter details directly into a table?

 

[LoneAngel666]

Yeah, at the moment.

Although, if it can be done in forms, I might be able to work around it. If I were to do this kind of validation in a form, how would I do it?

Rob

 

[Pat Hartman]

Jet does not support triggers which is what you would need to do the validation at the table level. The validation that Jet allows does not permit referencing multiple fields in a single validation rule. Therefore you can't compare fieldA to fieldB you can only limit a field to a set of values.

It is generally poor practice to build applications where users interface directly with the tables. It is far better to build forms where you can use form events to validate data and generallly have more control over the process.

When validation involves multiple fields, the best place to do it is in the FORM's BeforeUpdate event. That way, it doesn't matter which field is entered first. The validation won't be done until the record is about to be saved. If the data is invalid, you can cancel the update to prevent the record from being saved. The code will look something like:

If Me.AgreementStarts <  Me.AgreementEnds Then
Else
    Cancel = True
    Me.AgreementStarts.SetFocus
    Msgbox "The start date must be >= the end date", vbokonly
End If

If agreements are for standard periods of time, you could default the end date to the most common duration. You would put this code in the AfterUpdate event of the AgreementStarts field. The following will make the end date three months after the start date. The user can then over type the end date if necessary.

Me.AgreementEnds = DateAdd("m", 3, Me.AgreementStarts)

 

[Agent Smith]

begginers doubt

sorry for question on your on thread.

i was readig it and was interested to know where do we place the code above...

macro; module; field proprties on the form...


thenks for clearing the question
greatins from Portugal

Smith

 

[Mile-O]

In the form's Class module. As Pat say, on the BeforeUpdate() event

 

[Agent Smith]

thanks

this was heplfull reading;
Smith