469,300 Members | 2,278 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

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

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!

Oct 11 '10 #1
13 15939
32,173 Expert Mod 16PB
Are you talking about a simple filter so that only records which are set to [Rush] = Yes are included?
Oct 11 '10 #2
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.
Oct 11 '10 #3
32,173 Expert Mod 16PB
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]
Oct 11 '10 #4
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.
Oct 11 '10 #5
32,173 Expert Mod 16PB
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.
Oct 11 '10 #6
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".
Oct 11 '10 #7
32,173 Expert Mod 16PB
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.
Oct 11 '10 #8
You could also try:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2. If Me.Rush <> "Yes" Then
  3. Me.Rush = ""
  4. End If
  5. End Sub
Oct 11 '10 #9
32,173 Expert Mod 16PB
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.
Oct 11 '10 #10

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!
Oct 13 '10 #11
32,173 Expert Mod 16PB
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 :-)
Oct 14 '10 #12
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.
Jan 15 '20 #13
32,173 Expert Mod 16PB
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.
Jan 16 '20 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Daniel | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.