Multiple Values in Checkbox to be stored in one single field

[Tinay21]

I want to stored multiple data in one field named "Seriousness Criteria" using Checkbox. How is it possible for me to do so?
Thank you!

[zmbd]

This sounds like a violation of the Database Normalization and Table Structures practices used in good database design.

+ If you are talking about storing a list of possible values to be used as a checkbox, then the answer is yes and no. However, Yes is very difficult for the novice to implement and can make the future maintenance/upkeep a very difficult proposition as things invariably change.

+ If you are talking about storing the various checked values in the field then the answer is again, yes and no. However, the yes is somewhat difficult for the novice to implement and really should not be done in most cases as this violates the above normalization best practices.

If you can clarify the direction you are going and tell us how much experience you have in Access (or for that fact any) database application design we may be able to guide you in the right direction.

[jforbes]

I agree with Zmbd that it would be best for you in the long run to break out these fields into separate data elements. The two big reasons for this:

1. Binding to a Control can be done without code. This means that a Continuous Form can be used to display and edit data. Unbound fields and Continuous Forms don't go together well and if the value of an unbound control changes on a continuous form, it changes for every occurrence of the control on the form.
2. Reporting and Querying is much easier as you won't have to perform bitwise operations, which aren't bad, but not as clean as other forms of criteria.

If we haven't deterred you yet, then the following is one way to accomplish this.

Place these functions in a Module:

Public Function getBit(ByRef iValue As Long, ByRef iBit As Integer) As Boolean
    getBit = (iValue And (2 ^ iBit)) > 0
End Function
Public Function setBit(ByRef iValue As Long, ByRef iBit As Integer, ByRef bSet As Boolean) As Long
    ' iValue - Number to use to set (or reset)
    ' iBit - 0 to 30, bit to set or reset in iValue
    ' bSet - True to set the bit, False to Reset the bit
    If bSet Then
        setBit = (iValue Or (2 ^ iBit))
    Else
        setBit = iValue And ((2 ^ iBit) Xor 2147483647)
    End If
End Function

These are the bitwise Getter and Setter used to get and set the bits in the SeriousnessCriteria field.

The following code is an example pulled from a test database that has four checkboxes that are mapped to a single database field:

Private Sub Check0_AfterUpdate()
    Call updateCheck(Me.Check0.Value, 0)
End Sub
 
Private Sub Check2_AfterUpdate()
    Call updateCheck(Me.Check2.Value, 1)
End Sub
 
Private Sub Check4_AfterUpdate()
    Call updateCheck(Me.Check4.Value, 2)
End Sub
 
Private Sub Check6_AfterUpdate()
    Call updateCheck(Me.Check6.Value, 3)
End Sub
 
Private Sub Form_Current()
    Me.Check0.Value = getBit(Me![Attributes], 0)
    Me.Check2.Value = getBit(Me![Attributes], 1)
    Me.Check4.Value = getBit(Me![Attributes], 2)
    Me.Check6.Value = getBit(Me![Attributes], 3)
End Sub
 
Private Sub updateCheck(ByRef bValue As Boolean, ByRef iBit As Integer)
    Dim lAttributes As Long
    lAttributes = setBit(Me!Attributes, iBit, bValue)
    If lAttributes <> Me!Attributes Then Me!Attributes = lAttributes
End Sub
 

How it works...

• Form_Current decodes the value to be displayed for each of the Checkboxes and sets the checkboxes accordingly. This is performed whenever the current record changes.
• updateCheck() is a function that sets or resets a bit in the number that is saved to the database. This is called when a user changes the value of a checkbox.

[NeoPa]

It may make sense to have this available for reporting purposes, but, as the others have said, storing and maintaining it is a big no-no.

The SQL to include in a query for this would look similar to :

Mid(IIf([Death],', Death','') & IIf([Hospitalisation],', Hospitalisation/Prolonged','') & ...,3,9999) AS [SeriousnessCriteria]

You can see where I've guessed at your field names. You would need to replace and extend these with entries for each field you wanted to include in the result.