how could i create validation rule for more than two fields (date) in access table

[K Bharathi]

I created a access table in which the date fields are like that. DateofBirth, DateofMuster, DateofAppointme nt and DaeofJoining. Here, the conditions are like that dateofbirth should not be after today, so I put the validation rule in concerned field's general part as >=date(). The dateofMuster should not be before dateofBirth, DateofAppointme nt should not after DateofJoining. For this how could I enter the validation rule in their respective field VR.

[GKJR]

First off I think you need to change the criteria for DateofBirth to <Date() [instead of >=Date()].

The other fields cannot be validated by values you are entering into fields of the same record (not at the table level that is). The reason for this is that the record hasn't been committed while you are entering the field values. The field values technically don't exist until you exit the record and it gets committed, so there is nothing for the program to compare to.

You may be able to perform your data validation at the form level by creating rules for your controls based on other controls. You could also use VBA to validate your control values on the form before the record is committed.

[NeoPa]

Good advice, yet not fully correct.

When in Design View of a table you can select to view Table Properties (Alt-Enter toggles between hiding and viewing the window). There is a Validation Rule in there that allows specifying how fields within a record can relate to each other.

[zmbd]

you don't mention which version of access

[NeoPa]

I don't think it matters in this case Z. The solution is the same in all versions I tested on (2003 - 3010) and I know it's the same for earlier versions.

[zmbd]

ACC 2010 has data/table level macros, an after_update event could be set at the table level, think SQL-Trigger, that certainly would work and I believe, but have not tried, that the before change event would work too: Validating User Input With the Before Change Data Macro Event in Access 2010

I'm certain this can be used with a new record:
Before Change Macro Event Office 2010

Hence my request for version as there may be another option (^_^) (not sure of scalability to MYSQL or SQL-Server... but that may not be an issue).

As much as I dislike macros... for sharepoint published I'm having to relearn the macro language. :YUCK:

[NeoPa]

Fair point. Not sure why you'd be wanting to look at triggers or macros when a straightforward table Valdation Rule is available mind you.