Question on no data reports and numeric fields (1 Viewer)

[hammerva]

Hello again.

I have a report that consists of 2 subreports merged into one. Each report uses its own query to get the data. I just ran this report for a month and one of the subreports showed data but the other showed blanks in the fields. The reason for the blanks is because the query didn't produce any results.

I was wondering if it is possible to populate the fields in this subreport with something like a default value of 0. Or if I can update the query so that it shows a record with 0 in the fields instead of a query with no results (no records)

Hopefully I made sense in the question.

Thanks

 

[Rich]

Search here for examples of the HasData method

 

[hammerva]

I looked at some examples of the HASDATA and modified the control source for text box 'Count_Open_Low' to say:

=IIf([Risk_Activity_Report___Summary].[Report].[HasData],[Risk Exposure Query]![Count_Open_Low],0)

But I get a #name? message in the field I know that the query used is Risk Exposure Query.

Then I tried to change the IIF to:

=IIf([Risk_Activity_Report___Summary].[Report].[HasData],[Count_Open_Low],0) (Count_Open_Low is the text book field)

But I get an #error? message in the field

Can I use the IIF HasData process for a particular text box field?

Thanks for the response

 

[greaseman]

I don't think you can use the HasData on a specific text field in a subreport.

Here's how I use the HasData in my reports that contain subreports:

01) I of course place the subreport into my main report.

02) Next, I make a text box on my main report, the same size as my subreport link. I place this box behind my subreport link (in other words, the subreport is on top of this text box).

03) In this text box, for its control source, here is a sample of what I use to indicate there is no data from the subreport:

=IIf([Place your subreport's name here].[Report].[HasData],""," >>>> Place your missing data message here <<<<")


This does the trick for me...... Hope this helps you. Incidentally, I got this info in the first place from this forum. It's been a lifesaver for me more than once!

LOL!

 

[hammerva]

Actually the fields I am having problems with is on the main report not the subreport. So it looks like I am doing the same thing you just mentioned. But could be wrong.

 

[Rich]

What's the name of the subReport as shown on the property sheet of the main report?

 

[hammerva]

The source object is Report.Risk Activity Report - Summary
The name is Risk Activity Report - Summary

Why would that be important if it is a text field in the main report that is causing the issue. The subreport is actually showing data (although would have the same problem if no data exists as well).

 

[Rich]

=IIf([Risk Activity Report ].[Report].[HasData]," ",0)


you're getting an error because your not referencing the subReport correctly

 

[hammerva]

I am confused by the "" in the true condition of the IIF statement. I don't want to put a space in the field if there is data. Actually I already have a space in that field if there isn't data.

Here is a stripped down version of my database in Access 97. You will have to enter parameter values for the workbook fields in the query. A start date of 4/1/04 and end date of 4/30/04 will give you the results I am having trouble with.

Maybe this will give you an clue on what I am screwing up.

 

[Rich]

You need to explain what exactly you want the textbox to display, if there's data in the subReport why do you want it displayed in the main Report?

 

[hammerva]

What I want is the first text box with the label 'Number of Risks in Open Status with Low Risk' to show the value of the field Count_Open_Low from the query if records were processed. If the query did not produce any records based on the date criteria, I want the text box to show 0 not blank or null. If you run the query tied to the report for the month of April, you get no records. In this scenario, I want to know if it is possible to put a 0 in the textbox fields.

That is all. I still don't understand why the subreport has anything to do with this one field. The main report and sub report are not connected to each other data wise. They are 2 different reports on 1 overall report.

 

[Rich]

Your original post related to a problem with subReports. Change the control source of [Count_Open_Low] to =Nz([Count_Open_Low],0)

 

[hammerva]

Still no luck. Change the control source =Nz([Count_Open_Low],0) for the report text box field 'Count Open Low' and still get a message saying #error. Wish there was a way to know what the actual error message was.

Maybe if there is no data on a report, the text box isn't NULL or blank. I tried a debug watch in the 'No Data' event but had no luck stopping the processing which makes me think that it didn't go to the No Data event to begin with.

Thanks for all the help but it looks like a lost cause.

 

[Rich]

Delete the original control, add a new textbox and set it's control source to the expression

 

[hammerva]

Same result. I set the format of the text box to 'General Number' so that it should be shouldn't be blank.

No idea

 

[jezjez]

I looked at some examples of the HASDATA and modified the control source for text box 'Count_Open_Low' to say:

=IIf([Risk_Activity_Report___Summary].[Report].[HasData],[Risk Exposure Query]![Count_Open_Low],0)

But I get a #name? message in the field I know that the query used is Risk Exposure Query.

Then I tried to change the IIF to:

=IIf([Risk_Activity_Report___Summary].[Report].[HasData],[Count_Open_Low],0) (Count_Open_Low is the text book field)

But I get an #error? message in the field

Can I use the IIF HasData process for a particular text box field?

Thanks for the response

I have same problem and get #Name? ... suggestions welcome...

 

[KenHigg]

=val(Nz([Count_Open_Low],0))


???

ken

 

[jezjez]

=val(Nz([HOURSSUM2 all.Sum Of Hours],0))

Now i get #Error...

 

[KenHigg]

=val(Nz([HOURSSUM2 all].[Sum Of Hours],0))


??

ken

 

[jezjez]

Enter Parameter Value : HOURSSUM2 all

box returned...