Field entry dependent on another field value

[DRW]

I am trying to achieve a Yes or No entry in a field "Closed" based upon
whether there is a date inserted in a "Date Closed" field.

If there is a date in the "Date Closed" field, then there should be a "Yes"
in the "Closed" field.

I have tried the Expression Builder in Datasheet view, but the help informs
me that the Tables and Fields are not available in this view to allow me to
choose the field and build an iif type condition.

Any assistance greatly appreciated.

Many thanks.

 

[Allen Browne]

Could there ever be a case where your table should validly have a record
marked as closed but without a date?

If so, you can use the AfterUpdate event procedure of the [Date Closed] text
box on your form to set the value of the yes/no field.

But if not, the best solution would be to leave the yes/no field out of the
table. Then create a query, and type this into the field row in query
design:
IsClosed: ([Date Closed] Is Not Null)

Use the query whereever you would have used the table, and you now have a
yes/no value that can never go wrong and is kept up to date automatically.

More information in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[DRW]

Many thanks Allen.

However, I've tried your second suggestion, but there must be a slight
syntax error somewhere along the line.

Unfortunately, I only work on this database intermittently, so it will
probably be Thursday before I can look at it again.

Could there ever be a case where your table should validly have a record
marked as closed but without a date?

If so, you can use the AfterUpdate event procedure of the [Date Closed] text
box on your form to set the value of the yes/no field.

But if not, the best solution would be to leave the yes/no field out of the
table. Then create a query, and type this into the field row in query
design:
IsClosed: ([Date Closed] Is Not Null)

Use the query whereever you would have used the table, and you now have a
yes/no value that can never go wrong and is kept up to date automatically.

More information in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to achieve a Yes or No entry in a field "Closed" based upon
whether there is a date inserted in a "Date Closed" field.

If there is a date in the "Date Closed" field, then there should be a
"Yes"
in the "Closed" field.

I have tried the Expression Builder in Datasheet view, but the help
informs
me that the Tables and Fields are not available in this view to allow me
to
choose the field and build an iif type condition.

Any assistance greatly appreciated.

Many thanks.