Validation Rule Question for text box on Access 2003 form
I have a set of values in a bound form for which I want to establish a validation rule. It is a project related to medical screenings for newborns. When the user enters a patient ID and then a screening sequence is assigned. If it is the first screening, I need to set the validation rule to state be that the screening date is GE to the Patient's birthdate. If the screening is the second (or third, fourth...) then the validation rule need to indicate that new screening date is greater than the maximum screening date currently in the archive table.
As an FYI, if the patient doesn't have any records in the table, the fact that my dMax function returns a NULL lets me know that this is the first screening. The validation rule code I tried in the "Validation Rule" property of the form is included.
As an FYI, if the patient doesn't have any records in the table, the fact that my dMax function returns a NULL lets me know that this is the first screening. The validation rule code I tried in the "Validation Rule" property of the form is included.
>=IIf(DMax("DateCollected","tbl_NEWBORN_Archive_Screenings","PatientID = " & [forms]![frm_NEWBORN_Screening]![cbo_Screen_SelectPatient]) Is Null,
DMax("PatientDOB","NEWBORN_Demo_Current","ID = " & [forms]![frm_NEWBORN_Screening]![cbo_Screen_SelectPatient]),
DMax("DateCollected","tbl_NEWBORN_Archive_Screenings","PatientID = " & [forms]![frm_NEWBORN_Screening]![cbo_Screen_SelectPatient]));
**The error I get is an Invalid Syntax error**
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm in Access 2003, I don't see an form level Validation rule to use the new code. The issue you identified solved the problem. The semicolon was causing it not to work. I tested the code in a query before I added it to the validation rule.
You are right, there is no form-level validation rule, my mistake. Did it work as a control-level validation rule?
(°v°)
(°v°)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you create a new screening, use the form's "before update" event to check existing screenings.
Cheers!
(°v°)