Drive the status of a checkbox from date field (2000 (any))

Topic

New Reply

I am trying to drive the status of a checkbox (yes or no) based on the value of several date fields (about 14 of them). The checkbox is an indication of whether or not a project is active or inactive. If these date fields (completion dates of several steps in the project) are ALL filled in, then the checkbox would be empty (“NO”). If ANY date is not filled in, then the checkbox should remain checked (“YES”).

I tried doing this using an If > Then statement for the Form_AfterUpdate event but it is not working. Any hints?

Thanks in advance!!

Drew

Reply | Quote

Replies

Drew,

Since you are using the Form_AfterUpdate event, I am assuming this all occurs on a form. If so, where or how are the 14 date fields being input? Do they reside in a table, text boxes on the form, or other? If in a table, please indicate the table name and format.

Reply | Quote

The date fields as well as the checkbox are all on the same form. A user may call up the form to make an update to a date field (when a particular step is complete) and if that is the last date to be completed, then the checkbox, labelled “Active”, should toggle to NO (cleared). If any of the date fields in question are still empty, the checkbox should be left alone. The default value for the checkbox in new records is YES as defined in the table behind this form. Basically, this checkbox should only ever toggle off when the last date gets entered in the form.

I was not the original developer of this database but I have since acquired the responsibility to maintain it since the original author, a co-op student, is no longer with our company.

Thanks in advance for your help,

Drew

Reply | Quote

Drew,

If I understand you correctly, you have a form where the dates are entered. The data source for the form is a table that holds data. The data would be a field for what is being tracked (i.e., project 1), date fields (14 of them) and then a field for the checkbox status. Is this correct or is a different setup being used? Sorry, I can’t help answer the question until I understand how the data resides.

Reply | Quote

HI Gary,

Yes, that is correct.

Drew

Reply | Quote

Drew,

You have a couple of options here. For this example, I am going to assume that you only have three date fields, D1, D2, and D3.

Option 1: In lieu of using the checkbox status field from the table, insert a new checkbox on the form and insert the following in the control source of the data tab:
=IIF([D1]+[D2]+[D3] IS NULL, 0,-1)
Even though these are date fields, summing any null date value will give you a null value and provide you with the zero (uncheck).

Option 2: Create an update query that is triggered in the after update event of any of the date fields to update the value of the checkbox field in the table holding the data. The query would be something similar to this:

– Create a new query based on the table holding the data.
– Select the field for the checkbox value
– Change the query type to an update query
– In the Update To field, insert: IIF(([D1]+[D2]+[D3]) IS NULL, 0,-1)
– Trigger this query to run anytime a date value is changed on the form along with a Refresh statement.
This will update the data feeding the form.

HTH

Reply | Quote

Drew,

You have a couple of options here. For this example, I am going to assume that you only have three date fields, D1, D2, and D3.

Option 1: In lieu of using the checkbox status field from the table, insert a new checkbox on the form and insert the following in the control source of the data tab:
=IIF([D1]+[D2]+[D3] IS NULL, 0,-1)
Even though these are date fields, summing any null date value will give you a null value and provide you with the zero (uncheck).

Option 2: Create an update query that is triggered in the after update event of any of the date fields to update the value of the checkbox field in the table holding the data. The query would be something similar to this:

– Create a new query based on the table holding the data.
– Select the field for the checkbox value
– Change the query type to an update query
– In the Update To field, insert: IIF(([D1]+[D2]+[D3]) IS NULL, 0,-1)
– Trigger this query to run anytime a date value is changed on the form along with a Refresh statement.
This will update the data feeding the form.

HTH

Reply | Quote

HI Gary,

Yes, that is correct.

Drew

Reply | Quote

Drew,

If I understand you correctly, you have a form where the dates are entered. The data source for the form is a table that holds data. The data would be a field for what is being tracked (i.e., project 1), date fields (14 of them) and then a field for the checkbox status. Is this correct or is a different setup being used? Sorry, I can’t help answer the question until I understand how the data resides.

Reply | Quote

The date fields as well as the checkbox are all on the same form. A user may call up the form to make an update to a date field (when a particular step is complete) and if that is the last date to be completed, then the checkbox, labelled “Active”, should toggle to NO (cleared). If any of the date fields in question are still empty, the checkbox should be left alone. The default value for the checkbox in new records is YES as defined in the table behind this form. Basically, this checkbox should only ever toggle off when the last date gets entered in the form.

I was not the original developer of this database but I have since acquired the responsibility to maintain it since the original author, a co-op student, is no longer with our company.

Thanks in advance for your help,

Drew

Reply | Quote

Drew,

Since you are using the Form_AfterUpdate event, I am assuming this all occurs on a form. If so, where or how are the 14 date fields being input? Do they reside in a table, text boxes on the form, or other? If in a table, please indicate the table name and format.

Reply | Quote

Seeing as you have 14 date fields I thing you should consider looping through the controls and checking the values. I have attached an example you should be able to adapt.

Reply | Quote

Seeing as you have 14 date fields I thing you should consider looping through the controls and checking the values. I have attached an example you should be able to adapt.

Reply | Quote