Want Report to only show yes if checkbox is checked, and blank if it's not.

[wordbrew]

Hopefully this has a simple solution either by VBA or in the properties box.

I have a report in Access called JeppList in which the field [Rush] is pulling the data from my form entitled OrderForm from a checkbox (also called [Rush]).

The report is working fine listing "yes" if the checkbox is checked, and "no" if it is left blank.

However, this list goes to an offsite vendor. Instead of a long list of yes's and no's where they have to comb through and find the yes's. I simply wish for the yes's to show up, and the no's to show a blank/null value. Is this possible? I appreciate any assistance!

Thanks!

[NeoPa]

Are you talking about a simple filter so that only records which are set to [Rush] = Yes are included?

[wordbrew]

Thanks for responding NeoPa. Actually no, I don't want this to filter out any records.

I want all of the records in the report to show, but where the field [Rush] would show a "No" if the checkbox is left blank, I would rather it stay empty. I would like this so that when scanning the report, you can very quickly see which records have a "Yes" value, because the "No" values are left blank. I hope this helps. Thank you in advance.

[NeoPa]

Ah, that makes more sense now.

In the [Rush] control, instead of having a Control Source of [Rush], have instead =IIf([Rush],'Yes',Null)

Alternatively, if the report is bound to a query, you could have the [Rush] field as IIf(Table.Rush,'Yes',Null) AS [Rush]

[wordbrew]

Thanks NeoPa for helping with this NeoPa. I've put this into the control source, but now instead of showing a few "No"s and one "Yes", it's now showing "Yes" for every record regardless of whether the checkbox was checked or not.

[NeoPa]

Well, I guess you must have got something wrong somewhare. You haven't indicated even which of the proposed solutions you tried, so I can't tell you where. If you post wht you tried, I will have a look and see if the problem is obvious.

[wordbrew]

Sorry NeoPa, I should have been more specific. That's my fault.

I tried your first solution, so that instead of having a control source of [Rush] in the properties box, I now have the control source set to =IIf([Rush],'Yes',Null). Currently, this is having all of the results return as "Yes", even the one's the used to return as "No".

[NeoPa]

That's probably referring to itself then. My bad.

If you must have a solution within the report itself (as opposed to the query solution) then you may need to keep the [Rush] control as simply reflecting the [Rush] field (although this may be hidden), and have a separate control with the proposed Control Source but with a different name.

The query solution would be my preference in the circumstances, but there may be reasons why you will prefer the other. Good luck anyway.

[BarbQb]

You could also try:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Rush <> "Yes" Then
Me.Rush = ""
End If
End Sub

[NeoPa]

BarbQb:
You could also try:

But that wouldn't work, as the original value of [Rush] is a Boolean value. You would need to compare the value with Yes (or True), but without any quotes. Also, as the control is bound to a boolean field it may not allow values other than boolean ones. I think this approach, while clever enough, is not a good one for this problem.

[wordbrew]

NeoPa,

Just wanted to get back to you to thank you bigtime with your help earlier this week. I took your advice and used the report's query instead. Beside the query's field for [Rush], I added another column and put in the expression Expr1: IIf([Rush],"Yes",""), and then just referenced this expression instead of the original one pulling directly from the [Rush] query column.

And it's works great now. All records show up in the report, but only those that have checkmarks are showing a "Yes", and all others merely show blank. So thank you again for pointing me in the right direction, you've helped me in the past and you have always been awesome about it.

And BarQb, thank you for trying to help as well, I didn't get to try your method, but I appreciate your trying to help me in anycase.

Thanks guys!

[NeoPa]

A fine response WordBrew. Particularly to thank BarbQb for their efforts. Many members forget that time and effort is also expended by those trying to be helpful, but who just don't happen to be the ones whose guidance is followed in this instance. I echo your sentiments :-)

[LMMM]

This is really old, but I had the same issue above, but found a easy solution
Go to Design view in your "report" make your Yes/No boxes not visible, go to the Design tab in your tool bar click on the Controls and click on the Check box and place them over your Yes/No box, delete the label it gives with the check box, save and look at your report only the YES will appear as a checked box.

[NeoPa]

It's never too late to add new ideas :-)

It seems though, that your suggestion is to show a CheckBox, but the OP was asking how to replace a CheckBox with the word Yes in their report.