Set up DATE criteria

[vanny]

Hey i need to setup a date (Shipment Date) which does not leave the user to enter a date which is less than a months date from another date (Order Date)

That is if the ORder date is 17/01/07 it does not leave the user to enter the Shipment Date as 18/01/07, but it must calculate at least 30 days such as 18/02/07 is correct, and displays ERROR messages where necessary.


Any suggestions will be appreciated.

Thanks a lot.

 

[Bat17]

use the beforeUpdate event for [Shipment Date]
check that there is a value in [Order Date]
check the difference by adding 30 days ( or use the DateAdd() if you want to add a real month.)

Shout if you get stuck

Peter

 

[vanny]

Set up DATE criteria Reply to Thread

Hey I got the point, but please can you show me the code to go around it, as I am still a beginner.

Thanks, very much appreciated.

 

[Bat17]

Private Sub Shipment_Date_BeforeUpdate(Cancel As Integer)
If Nz(Me.[Order Date], "") = "" Then
    MsgBox "Please enter an order date before Shipment Date", vbCritical
    Cancel = True
    Exit Sub
End If
If Me.Shipment_Date < Me.Order_Date + 30 Then
'If Me.Shipment_Date < DateAdd("m", 1, Me.Order_Date) Then
MsgBox "Please enter a Shipment Date that is at least 30 days after the Order Date", vbCritical
    Cancel = True
    Exit Sub
End If
End Sub

note that I have used two methods of setting the 'month' just comment out the one you dont want.
+30days is fairly obvious, the advantage of dateadd is that a month from 31 jan 07 is 28 feb 07 not 2 Mar 07

HTH

Peter

 

[vanny]

Thanks a lot that worked excellent

Thanks once again