Check box on report, Need IIF

Experts,

I have a report with a checkbox:  [SentPPTSlide] and name is chkSentPPTSlide
I know that a check box can either be checked or not checked.  

I have crafted this IIF below in the control source of chkSentPPTSlide but I dont think I can display "NA" (on the report [SentPPTSlide] is greyed out for any value that is "No" for [cboPPTNeeded]).

=IIf([cboPPTNeeded]="No","NA",[SentPPTSlide])  

Any ideas on how I could show an "NA" when the condition is true?   I imagine a solution might be to use an overlay text box with On Detail_Format event but I have done this and the issue with this solution is that the overlay apparently only shows on print preview. For this reason, I want to use something else besides an overlay.  


thank you
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
You are correct about using code in your Detail_Format event procedure.  Clearly you cannot display text in a checkbox, so you need two controls.  

I suggest you create a label named lblPPT_NA with its Caption set to "NA" and position it over (or under the checkbox.

Then, in your event procedure:
Dim fPPTNeeded as Boolean
fPPTNeeded = cboPPTNeeded <> "No"
chkSentPPTSlide.Visible = fPPTNeeded 
lblPPT_NA.Visible = Not fPPTNeeded 

Open in new window


Best wishes,
Graham Mandeno
0
IrogSintaCommented:
This solution will work in both ReportView and PrintPreview.  Create 2 borderless text boxes that will be overlapped.  In the ControlSource of one text box, add the following:
=IIf(NOT [SentPPTSlide],"NA","")

Set the font of the 2nd text box to WingDings2 and its ControlSource to:
=IIf([SentPPTSlide],"P","")
The above will display a Checkmark but if you want to display a checkmark within a box, replace the "P" with an "R" instead.

Ron
0
pdvsaProject financeAuthor Commented:
Ron,

Nice...I think that will work but I have to ask a follow up.  
if  [cboPPTNeeded]= Null then the "NA" is appearing but I need the value to show just [SentPPTSlide] value.   So basically if [cboPPTNeeded] is "NO" then show "NA" but if anything other than "NO" then show the value of [cboPPTNeeded].

I hope that makes sense.  I have no idea how you would do that.  I am just trying to explain.   If you need some clarification please let me know.

Graham: I did not get to test fully but the code "as is" did not produce the results. I put the code in the Detail Format even tand I suppose that is where it needed to be.

thank you
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

IrogSintaCommented:
I assumed that [SentPPTSlide] is a field in the recordsource of your report.  Can you explain where cboPPTNeeded comes in?  The name itself gives the impression that this is a control on a form.  If so, what is it bound to?
0
GrahamMandenoCommented:
<< the code "as is" did not produce the results>>

What do you mean by "did not produce the results"?  Do you mean that both the label AND the checkbox remained visible?  That should not be possible if the code is running.

Try adding this line after "fPPTNeeded = ..."

Debug.Print cboPPTNeeded, chkSentPPTSlide, fPPTNeeded

When you open the report, you should see a number of lines in the Immediate window showing these values for each record.  If you see nothing then the code is not linked properly to the event property.  Open the property sheet for the Detail section, and check that the OnFormat property is set to "[Event Procedure]".  Then click the build button (3 dots) and make sure it takes you to the correct code.

If cboPPTNeeded can be Null, then you might have to change the second line to this:

fPPTNeeded = Nz(cboPPTNeeded, "") <> "No"

-- Graham
0
pdvsaProject financeAuthor Commented:
Ron:

<I assumed that [SentPPTSlide] is a field in the recordsource of your report.  Can you explain where cboPPTNeeded comes in
sorry, cboSentPPTSlide is the name of the field on the report [SentPPTSlide].  I put brackets around it and shouldnt have.  

Let me know what you think now.

Graham:  will try to get back in a bit. (In the office now)

thank you
0
IrogSintaCommented:
So basically if [cboPPTNeeded] is "NO" then show "NA" but if anything other than "NO" then show the value of [cboPPTNeeded

So you don't want to show a checkmark when cboPPTNeeded isn't NO?  You just want to show the value of cboPPTNeeded?  You're original question mentions a checkmark, hence the confusion.
0
pdvsaProject financeAuthor Commented:
<So you don't want to show a checkmark when cboPPTNeeded isn't NO?
If cboPPTNeeded is Null then show the value for [SentPPTSlide] name: (chkSentPPTSlide)
If cboPPTNeeded is "NO" then I need to show either of those 2 borderless checkboxes (as the value "NA" cant be displayed for the checkbox:
      •=IIf(NOT [SentPPTSlide],"NA","")
      Set the font of the 2nd text box to WingDings2 and its ControlSource to:
      •=IIf([SentPPTSlide],"P","")



let me know if  what you think now...hope is clearer.
0
IrogSintaCommented:
Not quite. When do you want to show a checkmark? If you don't want to show a checkmark then we don't need to use the Wingdings font.
0
GrahamMandenoCommented:
I'm guessing that if a PPT is needed, but has not been sent, you want some kind of indication of that other than a blank space - either an empty box or a box with a cross in it.

Therefore, if you are unhappy with using code to show/hide the controls, you will need to make Ron's IIf expressions a bit more complex:

For the plain text box:
=IIf(Nz([cboPPTNeeded],"")="No", "NA", "")

And for the Wingdings2 textbox:
=IIf(Nz([cboPPTNeeded],"")="No", "", IIf([SentPPTSlide],"R","Q") )

If you prefer a tick/cross without a box, then use "P" and "O" instead of "R" and "Q".

If you want an empty box instead of a cross for "not sent" then use Chr(163) instead of "Q".

Best wishes,
Graham
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Graham:  that works.  very nice.  That is mind boggling.  

Question:
I put the text boxes over one another, correct?  If so, I would think I would need a visible property to show the text box I need:  either the plain or the wingdings.  

It seems like I do have this issue.   The wingdings text box doesnt appear but the codes are correct.  

thank you.   I assume I would split the points.
0
pdvsaProject financeAuthor Commented:
Thank guys...will ask a separate question follow up on the visible part.  Will post right now.   Not sure if doing if correctly.
0
IrogSintaCommented:
I would need a visible property to show the text box I need:  either the plain or the wingdings.  
I just saw your follow up question on another thread.  You shouldn't need to change the visibility property for either textbox on our off. This was the whole idea of using IIf expressions in the textboxes.  Only one or the other would show up and it would work on both Report View and Print Preview.

Ron
0
GrahamMandenoCommented:
I'm guessing the "NA" text box is on top of the Wingdings one, correct?

You will need to set the BackStyle property of both textboxes to Transparent, so that the front one does not obscure what is behind.

-- Graham
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.