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.
>=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**

Open in new window

UTSWPedsBudget ManagerAsked:
Who is Participating?
 
UTSWPedsConnect With a Mentor Budget ManagerAuthor Commented:
The existing code I submitted worked, removing the semicolon was the issue.
0
 
harfangCommented:
In Jet, you cannot have multi-table validation rules, so DMax is not available in that context. You will need to move the burden to the interface.

When you create a new screening, use the form's "before update" event to check existing screenings.

Cheers!
(°v°)
0
 
harfangConnect With a Mentor Commented:
Ah, wait, this is a form validation rule. This should work, in fact, even though the form's "before update" event allows for better syntax checking. Let me have a second look.

(°v°)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
harfangConnect With a Mentor Commented:
Try this in the form's validation rule (not the control's: controls cannot use multi-field validation rules). I used txtDateCollected as control name. What is the semi-colon doing there, by the way?

(°v°)
[txtDateCollected] >= Nz(DMax("DateCollected", "tbl_NEWBORN_Archive_Screenings", "PatientID = " & [cbo_Screen_SelectPatient]), DMax("PatientDOB", "NEWBORN_Demo_Current", "ID = " & [cbo_Screen_SelectPatient]))

Open in new window

0
 
UTSWPedsBudget ManagerAuthor Commented:
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.

0
 
harfangCommented:
You are right, there is no form-level validation rule, my mistake. Did it work as a control-level validation rule?

(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.