Access Validation Rules

[laurenjm]

I have been trying to set a validation rule in my Database. I have 5 related tables but cannot set a validation rule for the Date. The scenario says that" A course cannot run with less than 5 people 2 weeks before the course is due to begin" Could any body help?

[noks]

Am trying to understand what u wanna do but unfort. am not succidin. Can u pls elaborate mo. ie. where r u gonna put yo code, give us yo code so dat we can c where we can modify etc

[Rabbit]

This can't be done using a validation as both class size and current date will be changing all the time.

You'll have to use a query that selects the records with <= 5 students and whether or not the class is scheduled to start within 2 weeks. Then run that query whenever you want to see if there are any such classes. You could, if you want, program this to run automatically each time the database is opened. Either through a macro or through code on a form. I prefer the latter.

[ADezii]

I have been trying to set a validation rule in my Database. I have 5 related tables but cannot set a validation rule for the Date. The scenario says that" A course cannot run with less than 5 people 2 weeks before the course is due to begin" Could any body help?

You have provided very limited information but the Logic is as follows:

    'Difference between Begin and Apply Dates > 14
1. [Course Begin Date]-[Course Apply Date]>14    
 
    'The number of people taking this specifc Course must be > 5     
2. DCount("*", "<your Table>", "[Course ID] = " & <Course ID>) >=5      
 
NOTE: Both conditions must be satisfied, mainly:
Condition1 AND Condition 2 

[Rabbit]

You have provided very limited information but the Logic is as follows:

    'Difference between Begin and Apply Dates > 14
1. [Course Begin Date]-[Course Apply Date]>14    
 
    'The number of people taking this specifc Course must be > 5     
2. DCount("*", "<your Table>", "[Course ID] = " & <Course ID>) >=5      
 
NOTE: Both conditions must be satisfied, mainly:
Condition1 AND Condition 2 

The problem isn't that they're comparing one static date [Start Date] to another static date [Apply Date]. They want to validate depending on Current Date.

[ADezii]

The problem isn't that they're comparing one static date [Start Date] to another static date [Apply Date]. They want to validate depending on Current Date.

Wouldn't a simple substitution of Date() instead of [Course Apply Date] be sufficient?

[Rabbit]

Would that work in a validation rule for a field in a table? I'm unsure of how that would play out.

[Rabbit]

I just tested it using minutes. It only checks when you first enter the data, after that there's no check unless you change the data. Problem is Date() will keep changing and what they want is a continuous check for classes that are about to start that have <= 5 students enrolled.

I'm thinking they'll have to go with a query that runs automatically when they open the database.