Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying checkbox information in a reportt (2000)

    I have a form that along with a number of other things, it has checkboxes the user can check. They can check none, one or all of them. I'm having trouble figuring out a way to display the information from the checkboxes in a report without taking up a lot of room.

    I'd like to have a control (text Box) in the report that will list all the corresponding information from the checked, checkboxes and expand if needed when many boxes were checked.

    Lets say I have checkboxes next to the seven days of the week. The user can check just one day, two days or all seven days. In the report, there would be a label indicating Day(s) and then a list of the days that were checked, separated by a comma.

    Day(s): Monday, Wednesday, Saturday
    or
    Day(s):
    or
    Day(s): Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying checkbox information in a reportt (2000)

    Are thos check boxes bound to seven individual fields in the record source? You could create a complicated expression to list the weekdays, but it is a bit easier to use a custom function for this. Here is such a function, to be put into a standard module:

    Public Function ListWeekdays(ParamArray Days()) As String
    Dim i As Integer
    Dim strResult As String
    For i = LBound(Days) To UBound(Days)
    If Days(i) = True Then
    strResult = strResult & ", " & Format(DateSerial(2004, 6, i), "dddd")
    End If
    Next i
    If strResult <> "" Then
    strResult = Mid(strResult, 3)
    End If
    ListWeekdays = strResult
    End Function

    You can use it in the control source of a text box as follows:

    =ListWeekdays([chkMon], [chkTue], [chkWed], [chkThu], [chkFri], [chkSat], [chkSun])

    where chkMon etc. are the names of the yes/no fields corresponding to the days of the week.

    See attached demo.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying checkbox information in a reportt (2000)

    Thanks Hans,

    I

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying checkbox information in a reportt (2000)

    That should be OK. You can easily design other ways of displaying the data, here are two others.
    Attached Images Attached Images
    • File Type: png x.png (2.1 KB, 0 views)

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying checkbox information in a reportt (2000)

    How would it work if we were not talking about days? Lets say they were peoples names and you were checking the ones you have worked with. (Just as an example) If I wanted the report to print out the name that corisponds to the checkbox. How would work. (Maybe using days in my example wasn't the best)

    Name(s): Dave, Bill, Jane
    or
    Name(s):
    or
    Name(s): Dave, Jane

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying checkbox information in a reportt (2000)

    In general, I would advise against using multiple yes/no fields for this. If you have a limited and fixed number of items, such as days if the week, it is not so bad, but in most other situations, using multiple fields is inflexible - for each new person you would have to change the design of tables, queries, forms and reports. The situation you describe corresponds to a many-to-many relationship; this is usually implemented by creating an intermediary table.

    Say you are keeping track of persons and activities. One person can participate in several activities, and several persons can participate in the same activity. You would create 3 tables:
    <UL><LI>tblActivities lists the activities; primary key ActivityID, plus descriptive fields.
    <LI>tblPersons lists the persons; primary key PersonID, plus descriptive fields such as name.
    <LI>tblPersonsActivities contains a record for each activity of a person. In other words, it lists person-activity combinations. Fields are ActivityID, PersonID and perhaps some fields specific to a combination. ActivityID and PersonID together form the primary key.[/list]tblPersonsActivities is linked to tblActivities on ActivityID and to tblPersons on PersonID.

    This setup takes a bit of time, but makes it very easy to add, edit or remove both persons and activities, and lends itself well to tallying information.

    You would present the data in a form plus subform, and in a report plus subreport.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying checkbox information in a reportt (2000)

    Maybe in trying to make this simple, I've made it too complicated. I was trying to make the reason for the check boxes kind of generic. Let me explain exactly what they are used for and then maybe it will be easier. (Sorry for the confusion)

    These checkboxes are on a form that is filled out by students after taking a class. There are 6 checkboxes on this part of the form and they never change. They are:

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying checkbox information in a reportt (2000)

    I still think the intermediary table solution would be better, but you could use this as control source of the text box on the report. Substitute the correct field names.

    <code>=IIf([ysnTooLong], "Class too long, ", "") & IIf([ysnTooShort], "Class too short, ", "") & IIf([ysnJustright], "Class just right, ", "") & IIf([ysnMoreInfo], "I would like more information, ", "") & IIf([ysnContact], "I would like to be contacted about this subject, ", "") & IIf([ysnOther], "Other", "")</code>

    There may be a superfluous comma after the last item. You can even include the comment for Other in the expression: replace

    <code>IIf([ysnOther], "Other", "")</code>

    with

    <code>IIf([ysnOther], "Other: " & [txtComment], "")</code>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying checkbox information in a reportt (2000)

    Thanks again Hans. I'll use this as a short-term fix and the table method for a more permanent fix.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •