Display "NIL" text box when no data on subreport

[Rabbit]

Dear All,

I have a main report, with 14 subreports.
I also recreated 14 invisible text boxes of "NIL" for one for each subreport (on main report),
I want to turn the "NIL" visible when the subreport has no data.
I tried to add event "On No Data" event but no luck!

Could anyone help me on this!

Rabbit

 

[Steve R.]

Are your subreports based on a DAO recordset or SQL query? If so you should be able to count the number of records returned. If the record count equals zero, then no data would have been returned.

 

[Rabbit]

I am quite new to Database, but I am sure my subreports are base on query from a table, which can be switched to SQL!
for example:
"SELECT qrySystem.Staff, qrySystem.Course, qrySystem.organ, qrySystem.CourseDate, qrySystem.Rank, qrySystem.lining
FROM qrySystem;"

 

[Steve R.]

Look up in ACCESS help "SQL Reference". Look for "Count Function". From Access help "You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.". In your case, you would be simply after the number of records.

You may want to look here too. Structured Query Language (SQL)

 

[gemma-the-husky]

are you getting a blank subreport, or no subreport

if the former, then see if you can design this to have a "NIL " banner, which is only visible if there are no items. May not work in the detail section though, and I havent tried this to see

 

[Rabbit]

I am confuse where to put the SQL count function to make the label visible. Thank you so much for you information anyway.
In fact I am thinking another direction:
Try to put in some VBA like:
If Me.rptCertification_subform.Report.HasData Then
Me.Label01.Visible = False
Else
Me.Label01.Visible = True
End If
and test.
hopefully it works, will get back to you soon I find out something

 

[gemma-the-husky]

the easiest way is to have a "watermark" on the subreport - eg a "paint" image set as a background

if there is no data to cover this up, you then just see the background.


i am not sure whether a subreport still gets shown though with no data - and you also need to be sure "real" data covers the watermark.

 

[Rabbit]

Dear Dave,
I put the scripts in the detail section, "On Format", instead in the subreport "on no data", it works!
Thank guys, you guys been really helpful.
Through the Q & A with you all experts, it opens up my thinking!
Just wish can be as good as you guys!
For me long way to go!!!
Many many thanks!!