Update Yes/No Field on form (checkbox)

[Guest]

When a date is entered in the CompletionDate control on my form, I would like
the Completed control to be checked (ticked, set to yes, whatever else you
want to call it). How would I do this? What about the reverse? When the date
is removed, the Completed control is unchecked?

Alternatively, would it be better database design to remove the Completed
field from my table and check the CompletionDate field for data (ie, if null,
task is incomplete)?

Thanks

Dave

 

[TC]

Yes, in theory you should not have both. The boolean field is
redundant. If you want a boolean value for display, just derive it in a
query:

SELECT *, Not IsNull(TheDateField) AS Completed
FROM TheTable

or use a similar expression as the controlsource of a (non updatable)
checkbox control.

HTH,
TC [MVP Access]

 

[Rick B]

Why? That seems a bit redundant. If you will be entering a completion
date, then you don't need a checkbox also. Anything that you would test the
checkbox for can test the date field instead. (As you indicated in your
second paragraph).

It is rarely a good idea to store duplicate information (even if it is in
different formats) in a record.

 

[Guest]

It is better to remove this field, and can always be achived using a query

Select TableName.* , IIf([CompletionDate] Is Null, False,True) As Completed
From TableName
========================
Also in the form, write in the control source of the check field
=IIf([CompletionDate] Is Null, False,True)

 

[Guest]

Thanks, thats what I thought.

However, on further thinking, sometimes we may know a task has been
completed, but we may not know when. In this instance, it is necessary to
retain the "Completed" field. Alternatively, it would be possible to input
the current date in these instances, but this may convey inaccurate data.

Regardless of database design, any ideas on the coding of the form as per my
original question? I can see further difficulties because the boolean field
now needs to be set to "Yes" when a date is entered and "No" when a date is
not present, but must be overidable by user input.

Thanks

Dave

 

[fredg]

Thanks, thats what I thought.

However, on further thinking, sometimes we may know a task has been
completed, but we may not know when. In this instance, it is necessary to
retain the "Completed" field. Alternatively, it would be possible to input
the current date in these instances, but this may convey inaccurate data.

Regardless of database design, any ideas on the coding of the form as per my
original question? I can see further difficulties because the boolean field
now needs to be set to "Yes" when a date is entered and "No" when a date is
not present, but must be overidable by user input.

Thanks

Dave

Code the AfterUpdate event of the CompletionDate control:
[CheckBoxField] = Not IsNull([CompletionDate])

 

[TC]

Like he said

I'd also put an AfterUpdate on the checkbox control, so if he un-ticks
that box, it will erase the date (if any).

HTH,
TC [MVP Access]