validating summary

  • Thread starter Thread starter tammie
  • Start date Start date
T

tammie

On my form, the user enters what percentage of the FTE
goes to which division. There are maximum four divisions
possible. How can I validate that what they enter = 100?
This is a subform I bring and relate by the ID field. I
tried using the validation field in the properties but
because it is a sum field it isn't working????
 
Hi,


That is a validation occurring between records, since their sum must be
100 (percent). The problem with that is about modifying the data: if I want
change (25, 25, 25, 25) to (35, 15, 25, 25), I have to do it, manually, one
at a time, so, I got, at an intermediate moment (35*, 25, 25, 25) but the
validation would deny me the 35! So, how do I maintain such a system.

Anyhow, with Jet 4, assume you want not more than 4 players on a team,
you can use:

CurrentProject.Connection.Execute "ALTER TABLE playersTeams ADD CONSTRAINT
NoMoreThanFourPlayers CHECK( 4 >= (SELECT COUNT(*) FROM playersTeams As a
WHERE a.teamID = teamID )) "


in your case, you would use CHECK( 100 >= (SELECT SUM(amount) FROM ...


that would make sure you do not exceed 4 players, or that the sum won't be
larger than 100.

You can drop the constraint:

CurrentProject.Connection.Execute "ALTER TABLE tableName DROP CONSTRAINT
constraintName "


You need ADO. DAO won't work with that statement, neither the query
designer.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top