• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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

0
UTSWPeds
Asked:
UTSWPeds
  • 4
  • 2
3 Solutions
 
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
 
harfangCommented:
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
 
harfangCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
UTSWPedsAuthor 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
 
UTSWPedsAuthor Commented:
The existing code I submitted worked, removing the semicolon was the issue.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now