Validation for date

[naungsai]

Dear Friends

I have a Travel plan table.
It has "DepartureDate", "ArrivalDate" and "Destination".


The "DepartureDate" of next destination should not smaller than "ArrivalDate" of previous "Destination". It can be equal to "ArrivalDate" of previous one.

How to do validation for this case.

DepartureDate..............ArrivalDate............Destination
1/1/2009.....................1/1/2009...............Tokyo
1/2/2009.....................1/3/2009...............New York
1/3/2009.....................1/4/2009...............London

Thanks in advance.

 

[naungsai]

Dear Frineds

I am refreshing my post.
I'm afraid you forgot it.

Best

 

[missinglinq]

I think this will do what you want, simply replacing the field and table names with your actual names:

Private Sub DepartureDate_BeforeUpdate(Cancel As Integer)
 If Me.DepartureDate < DMax("[ArrivalDate]", "YourTableName") Then
   MsgBox "Departure Date Cannot Fall Before Last Arrival Date of " & DMax("[ArrivalDate]", "YourTableName")
   Cancel = True
   Me.DepartureDate.Undo
 End If
End Sub

 

[naungsai]

I think this will do what you want, simply replacing the field and table names with your actual names:

Private Sub DepartureDate_BeforeUpdate(Cancel As Integer)
 If Me.DepartureDate < DMax("[ArrivalDate]", "YourTableName") Then
   MsgBox "Departure Date Cannot Fall Before Last Arrival Date of " & DMax("[ArrivalDate]", "YourTableName")
   Cancel = True
   Me.DepartureDate.Undo
 End If
End Sub

Dear Missinglinq

Thank for your solution. It works and I can go with it.
I also accept your logic.

If it does not make you busy, Can I validate it with the immediate previous record? (Just for fine tunning and curiousity.)

Happy Chinese New Year

 

[missinglinq]

Assumed that was what you were doing here. Access has no native method of determining the "immediate previous record." An Access table, as described by someone, maybe John Vinson, is simply a "bucket of bits" without order. When this is absolutely necessary, you have to a date or date/time stamp for each record which is set when the record is created and updated any time the record is modified. You would then use this date/time field to determine the "immediate previous record." The problem with this kind of thing, in your particular applicationis this, going back to your example. You have this:

DepartureDate..............ArrivalDate............ Destination
1/1/2009.....................1/1/2009...............Tokyo
1/2/2009.....................1/3/2009...............New York
1/3/2009.....................1/4/2009...............London

Then you go back and edit the second destination

DepartureDate..............ArrivalDate............ Destination
1/1/2009.....................1/1/2009...............Tokyo
1/2/2009.....................1/4/2009...............New York
1/3/2009.....................1/4/2009...............London

Now your third destination, London, departs before you arrive in New York. You see the problem? How is the third record going know that the second record has changed? Perhaps a better explanation of your exact needs would help us here to help you.

I'm going to be off-line most of the day, but if you can give a little more explanation someone will, no doubt, be along to help.

 

[naungsai]

Dear missinglinq

Thank you for your reply with patience. It is fine to go with your previous expression.

Best