Display TEXT on report only if checkbox from table selected.

[flect]

Greetings!

I have just jumped into the deep end of access having zero programming or db experience - What a wonderful world of brain-melting fun

I have a (hopefully) simple problem:

I have basic table and report -
the table has 2 Yes/No check boxes.

What I would like displayed on the report is a simple text label if the box is checked.

eg. i have [Tab+] & [POR] as YES/NO

on the report I don't want a check box - just a label that says "Tab+" &/or "POR" displayed only if they are selected.

The field on the original table has to be a Yes/no - otherwise i would've just left it as text and wouldn't have this problem.


Any suggestions would be greatly appreciated.

 

[pbaldy]

Try a textbox with a control source of:

=IIf(POR = True, "POR", "")

Spaces and symbols are not advised in object names, so if it's not too late I'd change the name of that field.

 

[flect]

wow!!!! that was fast!

and exactly what i needed - Thanks!

 

[pbaldy]

Lucky timing; you posted right as I was logging in. Glad it helped. Welcome to the site by the way.

 

[BeckyA]

Hi Guys,

My query is similar to this. The answers so far have helped but not enabled me to do exactly what I want.

I have a table, with multiple yes/no boxes, which I would like to be displayed as text if they are there. My problem is that, although I can bring up one answer, the minute I add a second field, it brings back 0 or -1.

As an example, the first three field names are "Spades", "Forks" & "Brooms".

Hope you can help!

Thanks,

Becky

 

[pbaldy]

What exactly is your formula that fails?

 

[shanice]

Thanks for this post. I got text to display for multiple checkboxes; however, I cannot figure out how to make commas appear between each value. The only way I've been able to do it is to add the comma at the end of the text, but it looks tacky because it always shows. Does anyone know how I can display a comma only when there is a subsuquent value?

THANKS!!!

 

[pbaldy]

What does your formula look like?

 

[shanice]

Here's what I'm entering into the control source of a text box:

=IIf([PM]=True,"Project Manager; ","") & IIf([TL]=True,"Technical Lead; ","") & IIf([Task]=True,"Tasking; ","") & IIf([Understand]=True,"Understanding; ","") & IIf([Staffing]=True,"Staffing; ","") & IIf([Manage]=True,"Management; ","") & IIf([SE/CQ]=True,"

 

[nyaworld]

Is there a way to do this via VBA ? I have 100 + yes or no check boxes and I was hoping not to have to add an IIF statement for each checkbox field.

 

[pbaldy]

Sounds like a potential normalization issue, but an If/Then/Else block would be the VBA equivalent of an IIf(). Or perhaps Select/Case, depending on what you're trying to accomplish.

 

[nyaworld]

Sounds like a potential normalization issue, but an If/Then/Else block would be the VBA equivalent of an IIf(). Or perhaps Select/Case, depending on what you're trying to accomplish.

I saw another older posting (not here) and I think I'm missing something because it doesn't work...

As this project progressed I found a slightly different way to do it which worked even better for me, it involved using an empty text box as a repository for each item I wanted to display as in the end they didn't want things displaying as a column-style list but as a comma-separated set of items in a line.* The actual labels themselves are all hidden.
It adds each selected option into a variable (lastitem) and drops a comma and a space in front of each one, then when it gets to the end it removes the last 2 characters and drops the contents of lastitem in to the textbox (txtallitems).

From <https://community.spiceworks.com/topic/429306-access-only-display-true-items-in-report?page=1#entry-2875687>




Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim topVal1 As Long
Dim leftVal0 As Long
Dim leftVal1 As Long
'Set first row values
topVal1 = 0
leftVal0 = 0
leftVal1 = 1500
'Loop through each control and determine visibility and position
Dim ctl As Control
Dim labelname As String
Dim lastitem As String
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
labelname = "lbl" & ctl.Name 'Get label name from checkbox name (ensure labels are named lblcheckboxname)
If ctl.Value = True Then
'Show label and set position
Controls(labelname).Visible = False
lastitem = lastitem & Controls(labelname).Caption & ", "
Else
'Hide fields, position not important
Controls(labelname).Visible = False
End If
End If
Next ctl
If lastitem = "" Then
Me.txtallitems = " "
Me.txtallitems.Visible = True
Me.txtallitems.Left = leftVal1
Me.txtallitems.Top = topVal1
Else
lastitem = Left(lastitem, Len(lastitem) - 2)
Me.txtallitems = lastitem
Me.txtallitems.Visible = True
Me.txtallitems.Left = leftVal1
Me.txtallitems.Top = topVal1
End If
End Sub

From <https://community.spiceworks.com/topic/429306-access-only-display-true-items-in-report?page=1#entry-2875687>

 

[pbaldy]

I don't know if there's a question in there or not. If so, it would help to know what "doesn't work" means exactly.