Set field value based on other field values

I am trying to create a validation rule in Access 2010 to set true / false to True ...

In more detail, I have a table with multiple yes / no fields and one true / false field, and I want the true / false field to change to true only if all the yes / no fields are yes.

I am trying to make it set automatically after fields update and no user change ...

+3


source to share


1 answer


A validation rule cannot change the value of a field. It can only indicate whether this value should be considered valid.

If you want a field to automatically update to indicate if all fields are True, consider a calculated field or data macro. Since you are using Access 2010, both of these options are supported.

An easier approach, however, is to decide that you don't need the pivot field to exist in your table design. You can use query to get it when you need to see it.

For example, with two Yes / No fields Fld1 and Fld2, a simple field expression will tell you if they are both True ...



SELECT Fld1, Fld2, (Fld1=True AND Fld2=True) AS all_are_true

      

This will show True as -1 and False as 0. If you want the query to display these values ​​as True or False, you can use the expression Format

...

SELECT Fld1, Fld2, Format((Fld1=True AND Fld2=True), 'True/False') AS all_are_true

      

This computation is a trivial workload for the db engine. And evaluating the field expression every time the query is run ensures that all_are_true reflects the latest changes in other fields.

+2


source







All Articles