I am using ACCESS 2007. I have added a text box in the Details section of a report. The text box has a ""IIf “” statement to display text when a value is null. The message displays properly when in the Report View. In the Print Preview view, it does not display. I want it to display. I have confirmed the text box property ““visible”” is set to YES. Can anyone advise how to solve this?
Helen Burns, Thank you so much! This is what I needed. Awesome! I considered my question answered. Just need to figure out how to post it as solved on this forum.
Sorry…I’m just getting back working this database task. I did see this report property, just was not sure how to write the code. I tried your suggestion and it worked. For me, this is good info to display for the user, however my management wants a hard copy report. Thanks!
Jfmark,
My apologies, a “conditionally formatted” text box does appear in a print preview as well as Report View.
From Re Reading your posts, my understanding is that you still want a report that states NO RECORDS if there are no records.
Note - if there are no records then nothing will appear in the detail section - No Detail
I have now created a new report, in design mode.
in the REPORT HEADER I have added a text box (mine is called Text9 - and set the control source
=Count(*)
(This counts the number of records returned for the report)
I have also added a new text box - IN THE HEADER - and set the control source to
=IIf([Text9]=0,“No Records”,“”)
Where Text9 is the name of the field with the Count(*)
When the report is run, if there are no records, the second text box displays “No Records”
-you can make the first Text box Visible = No if you don’t want the record count in your report header
Note - these text boxes can be in the report header or the page header as both of these will still exist even if there are no records.
Kind regards,
Helen Burns
Jfmark,
You are trying to display a message when there are no records, therefore adding code to the Detail Section of the report will not display anything because there are “no Records”.
If you put the Report in design view. Select the Report and go to the Property Sheet
There is a tab called Events. Select the Event On No Data and click the () Code Builder to add code.
This is where you can put the code for what you would like to happen when there is no data in the report.
In My code - I just would like to see a message on the screen and then close the report.
MsgBox “No Records Found”
DoCmd.Close acReport, “YourReportName”
Good Luck
Thanks. I did try your test of creating 2 text boxes. Let me see if I understand it correctly. You created Text27 in which you entered “2011” or 2012" into the box as Control Source? Then you created Text25 with the IIF statement with the purpose of displaying a “1” if Text27 has 2011 in it or displaying “0” if 2011 in not entered. I tried that but it always returned a “0” value. It was visible both in Report View and Print Preview though. For clarification here is my IIF statement. IIF([Field_Name], Null, “No Data to Display”). My purpose is I want a report print out even it there are no records. I want to display the text “No Data to Display” in the detalls section. So in my IIF statement, the condition is it is looking at a field name in my query that is Null. I believe that to be different from your test and hopes my clarification is of help in solving this. And yes the “Display When”
; is set to “Always”
I am not sure if this will work or not but instead of using the IIF use an If-Then statement such as this:
If IsNull(Me.ClientSmallBiz) Then
Me.txtSmBiz.Visible = True
End if
In the above statement, Me.ClientSmallBiz is derived from my form and Me.txtSmBiz is the textbox on my report.
I place these statements in both the OnCurrent and OnLoad events for my reports and forms. This is probably overkill but it works for me. I use many such constructs to create dashboards using .BackColor to colorize my textboxes.
HTH
David
I’m not sure I fully understand your statements about vba code as I am not as expert in developing databases. I can confirm my statements are correct in that the textbox does display in Report View and not in Print Preview.
First I would like to say “Thank You” to all of those who replied. I am a newbie and grateful for any help. Re Helen Burns reply to use the Report View…when I attempt to print the Report View the text still does not display…the only way I currently can figure how to display it is to use Screen Capture and paste it in Word as an example.
I just tried, using an existing Report in one of my databases, adding a text box with an IIF statement and it works in both Report and Print Preview Modes.
What I did for this test was create two text boxes (Text 25 and Text 27); one (Text27) with “2011” or “2012” in it and the other (Text25) with the following IIF statement:
=IIf([Text27]=“2011”,1,0)
This displays a “0” in Text25. If I change Text27 to “2012”, Text25 displays a “1”, as expected.
I am also running Access 2007.
Do you have the text box “Display When” property set to “Always”? If this is set to “Screen Only”, it will not display in Print Preview Mode. But then, if set to “Always”, it will also print when you run a hard copy.
Can I double check that you are certain you have this the correct way around please?
Since ACCESS 2007 vba code does not run in REPORT view but does in PRINT PREVIEW.
Not sure if this also applies to IIF statements but I would not be surprised if it did.
Roy
I don’t know if this will work for you, but I’d try using a Label, which will be visible in Print Preview, instead of the text box. You can change the contents of the Label somewhere in the report’s code. You could also possibly keep the text box and its IIF statement and use it to change the Label contents. Just a thought.
I don’t know of any way to solve this.
Rather than use PrintPreview, use Report View (acViewReport)
