validation rule for dates

[ashu.doc]

Hi
I have two dates in my form, Date A (date of birth) and Date B (date of a process), both dates are in same table and form called persondetails.
What will be the validation rule if I want:
1. Date A must be either equal to Date B or earlier than Date B

2. Date A must be today or earlier

3. Date B must be today or earlier



I know for second and third we use <Now() but I am struggling to find a full rule for all of the above conditions.

Thanks

[orange]

1. Date A must be either equal to Date B or earlier than Date B

2. Date A must be today or earlier

3. Date B must be today or earlier

use a small vba procedure
___UNTESTED ___

If isDate(DateA) and
  IsDate(dateB) and
  DateA<=DateB and
  DateB <= Date  Then           '  (or Now whatever your conditions require)
do stuff here
else
Error condition
endif

[ashu.doc]

Hi Orange
Can you please give a complete code as I am trying to put this code but its not working.
Specially cant understand this part
DateB <= Date Then ' (or Now whatever your conditions require)
do stuff here

Thanks

[ashu.doc]

I was using this code but its giving expression error.

If IsDate(DOB)
IsDate Dateofoperation
DOB =<Dateofoperation And
Dateofoperation=<Date
DOB <= Date
Then DoCmd.Save acTable
Else
MsgBox "DOB can't be later than Date of operation", [vbOKOnly], [Error]
End If
End Sub

DOB is date A
Dateofoperation is date B

[pbaldy]

Try the before update event and adapt orange's code:

http://www.baldyweb.com/BeforeUpdate.htm

[orange]

Something like the following, but as Paul suggested, you should use the BeforeUpdate event

If IsDate(DOB)  AND _ 
   IsDate( Dateofoperation) AND _
   DOB =<Dateofoperation And   _ 
      Dateofoperation=<Date AND _
      DOB <= Date  Then 
   DoCmd.Save acTable
Else
    MsgBox "DOB can't be later than Date of operation", [vbOKOnly], [Error]
End If
End Sub

[ashu.doc]

Hi
I have used this code:

Private Sub Text130_BeforeUpdate(Cancel As Integer)
If IsDate(DOB) And _
IsDate(Dateofoperation) And _
DOB <= Dateofoperation Then
DoCmd.Save acDefault

Else
MsgBox "Date of operation cannot be earlier than DOB"

End If
End Sub

I have used it as you said in before update event, and it is working fine uptill this msgbox but as I click Ok on msg box the cursor moves to next field in the form, rather than staying on the same field to correct it. I am also using a validation rule for the same field for entering today's or earlier date which is working and does not allow user to move forward unless user enter correct date. What can I add in this code so that either the text130 get refreshed after the appearence of the msgbox if user enters wrong date.

thanks

[orange]

This was in the original, and is not in your code

AND _ DOB <= Date Then

[ashu.doc]

Hi
Yeah it was there, but for "Date to be today or earlier", instead of code I have used validation rule, which is working fine.

[ashu.doc]

But my problem is as I said the focus moves on after the msgbox appears and user clicks OK. Which should not be the case, it should bound the user to enter proper date in the field and for that, I am asking a code which either refresh/requery the Text box or keep on displaying the error.

[pbaldy]

You don't have the

Cancel = True

line which cancels the update and leaves focus on the control.

[ashu.doc]

Ok finally that works. thanks ever so much.

[pbaldy]

No problem.