Multiple Check boxes to text in report

J

Jay

I have a form with 13 check boxes, one for each division that can provide
input to a given topic. I want to have division names displayed on the
report in a single text box separated by commas. I can get one name to
appear but cannot figure out how to get more than one. I used: =IIf([AA]=
True, "AA", "") for one. Any help would be appreciated. Thanks.
 
D

Duncan Bachen

Jay said:
I have a form with 13 check boxes, one for each division that can provide
input to a given topic. I want to have division names displayed on the
report in a single text box separated by commas. I can get one name to
appear but cannot figure out how to get more than one. I used: =IIf([AA]=
True, "AA", "") for one. Any help would be appreciated. Thanks.
Click to expand...

You'll need to go through all 13 boxes and build your string, and then
trim the final comma.

Create yourself a function such as this:

Private Function GetDivisions() as String
Dim strTempDiv as String

strTempDiv = ""

' Build your string
If [AA] = True Then strTempDiv = strTempDiv & "AA, "
If [BB] = True Then strTempDiv = strTempDiv & "BB, "
If [CC] = True Then strTempDiv = strTempDiv & "CC, "
' .. Other checks as necessary ..

' Delete the trailing comma and space
strTempDiv = Left(Len(strTempDiv), -2)
GetDivisions = strTempDiv

End
 
D

Duane Hookom

If your table structure was normalized, this might be a little easier.
Division names are data values, not field names.

--
Duane Hookom
MS Access MVP

Duncan Bachen said:
Jay said:
I have a form with 13 check boxes, one for each division that can provide
input to a given topic. I want to have division names displayed on the
report in a single text box separated by commas. I can get one name to
appear but cannot figure out how to get more than one. I used:
=IIf([AA]= True, "AA", "") for one. Any help would be appreciated.
Thanks.
Click to expand...

You'll need to go through all 13 boxes and build your string, and then
trim the final comma.

Create yourself a function such as this:

Private Function GetDivisions() as String
Dim strTempDiv as String

strTempDiv = ""

' Build your string
If [AA] = True Then strTempDiv = strTempDiv & "AA, "
If [BB] = True Then strTempDiv = strTempDiv & "BB, "
If [CC] = True Then strTempDiv = strTempDiv & "CC, "
' .. Other checks as necessary ..

' Delete the trailing comma and space
strTempDiv = Left(Len(strTempDiv), -2)
GetDivisions = strTempDiv

End
Click to expand...
 
J

Jay

Thanks Duncan. Duane, what would be a better way to do this? Thanks.

Duane Hookom said:
If your table structure was normalized, this might be a little easier.
Division names are data values, not field names.

--
Duane Hookom
MS Access MVP

Duncan Bachen said:
Jay said:
I have a form with 13 check boxes, one for each division that can provide
input to a given topic. I want to have division names displayed on the
report in a single text box separated by commas. I can get one name to
appear but cannot figure out how to get more than one. I used:
=IIf([AA]= True, "AA", "") for one. Any help would be appreciated.
Thanks.
Click to expand...

You'll need to go through all 13 boxes and build your string, and then
trim the final comma.

Create yourself a function such as this:

Private Function GetDivisions() as String
Dim strTempDiv as String

strTempDiv = ""

' Build your string
If [AA] = True Then strTempDiv = strTempDiv & "AA, "
If [BB] = True Then strTempDiv = strTempDiv & "BB, "
If [CC] = True Then strTempDiv = strTempDiv & "CC, "
' .. Other checks as necessary ..

' Delete the trailing comma and space
strTempDiv = Left(Len(strTempDiv), -2)
GetDivisions = strTempDiv

End
Click to expand...
Click to expand...
 
D

Duane Hookom

I would create a related table where a record would be added for each
division for the main record. This would allow you to add more districts if
necessary without changing table structures, forms, etc. You can use code to
concatenate the district names.
--
Duane Hookom
MS Access MVP

Jay said:
Thanks Duncan. Duane, what would be a better way to do this? Thanks.

Duane Hookom said:
If your table structure was normalized, this might be a little easier.
Division names are data values, not field names.

--
Duane Hookom
MS Access MVP

Duncan Bachen said:
Jay wrote:
I have a form with 13 check boxes, one for each division that can
provide
input to a given topic. I want to have division names displayed on
the
report in a single text box separated by commas. I can get one name
to
appear but cannot figure out how to get more than one. I used:
=IIf([AA]= True, "AA", "") for one. Any help would be appreciated.
Thanks.

You'll need to go through all 13 boxes and build your string, and then
trim the final comma.

Create yourself a function such as this:

Private Function GetDivisions() as String
Dim strTempDiv as String

strTempDiv = ""

' Build your string
If [AA] = True Then strTempDiv = strTempDiv & "AA, "
If [BB] = True Then strTempDiv = strTempDiv & "BB, "
If [CC] = True Then strTempDiv = strTempDiv & "CC, "
' .. Other checks as necessary ..

' Delete the trailing comma and space
strTempDiv = Left(Len(strTempDiv), -2)
GetDivisions = strTempDiv

End
Click to expand...
Click to expand...
Click to expand...
 
Top