• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 5
  • 5
  • 4
2 Solutions
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now