Null Value in Query trashes whole report

[Ice Rhino]

I have a report that is based on approx 12 queries that completes a number of calculations based from an array of different tables.

I appear to have two queries that if the result of either query is null, then none of the queries for the report run and the whole report comes out blank. If I remove the offending query and rerun the report, everything runs as I would expect.

I have considered sub reports, but the ability to format a subreport on an already densley populated page with sub reports makes this virtually impossible.

Can anyone shed any light on this, am I doing something serioulsy wrong here?

Regards

 

[Mike375]

What I normally do is have an IIF so that 0 is the result instead of a blank or null.

I think there is another function called Nz that allows a similar thing to be done when it might be hard to achieve with IIF

Mike

 

[Ice Rhino]

Thanks for such a quick response. I have placed both query definitions below, could you assist in the insertion of the suggestions you have made please

box_6_qry_1

SELECT src_tbl_act_eim.summary
FROM src_tbl_act_eim
WHERE (((src_tbl_act_eim.site_ref) Like [Forms]![frm_centre_dashboard]![cmb_sel_centre].[value]) AND ((src_tbl_act_eim.src_date) Like "*" & [Forms]![frm_centre_dashboard]![cmb_src_dat].[value]));

box_5_qry_2

SELECT Count(src_tbl_finals.sys_date) AS Finals
FROM src_tbl_finals
GROUP BY src_tbl_finals.site_ref, src_tbl_finals.src_date
HAVING (((src_tbl_finals.site_ref) Like [Forms]![frm_centre_dashboard]![cmb_sel_centre].[value]) AND ((src_tbl_finals.src_date) Like "*" & [Forms]![frm_centre_dashboard]![cmb_src_dat].[value]));


Many thanks in anticipation

 

[Mike375]

You don't "insert" IIf as such.

Firstly, I assume those queries are giving you a null result sometimes and figure or some other entry at other times.

The easiest way is to make a new field in a query that uses IIF.

NewField:IIF([box_6] Is Null,0,[box_6])

Some other people on the forum will be able to incorporate the IIF on your box_6 itself.

If you create a new field but have a lot of stuff feeding off the name box_6 then you might look at changing the name of box_6 to something else and then naming the new field box_6

Of course if the IFF can be done (or the Nz) within box_6 then that won't need to be done.

But looking a bit further it appears that the source of the problem is a query not returning any record as opposed to returning a blank field. If the rest of queries will only function if this query returns a record (or records) then you will need to make some changes to the search criteria. That would involve introducing Or

For exampe the criteria search This And That is returning no records but if you had This And That Or Whatever and make Whatever something that will guarantee a record is returned then you deal with those entries in the next query that uses this query.

Mike

 

[Ice Rhino]

I have read your comments and I thank you for your prompt response.

I shall re-read what you have said and try to implement the suggestions you have put forward.

Thank You once again

 

[Ice Rhino]

I have read this line over and over

NewField:IIF([box_6] Is Null,0,[box_6])

and although I have placed this in the query, all I now get it two column headings with no rows. Have I done something wrong here?

Regards

 

[Ice Rhino]

I am really struggling to get this to work, does anybody have any suggestions of what I can do here.

Regards

 

[Mike375]

I am really struggling to get this to work, does anybody have any suggestions of what I can do here.

Regards

This IIF([box_6] Is Null,0,[box_6]) won't select any records for you. What it does is get rid of a blank field but only on records that display. You may need to go back further into your series of queries.

In other words you currently have

Box_6

123
Blank
345
Blank
344

The IIF changes this to

Box_6.......NewFieldName

123..........123
Blank........0
345..........345
Blank........0
344..........344

You now base calculations, searches or whatever on the field NewFieldName

It sounds like from your earlier description that you have several queries based on other queries and you probably need to go back to the first queries.

Mike

 

[Ice Rhino]

This query is actually not fed off any previous query results. The SQL I pasted earlier in this thread is directly attacking the tables with no pre-reqs.

I will look through what you have posted, my only concern is that what you have posted is based around the thought of previous queries.

Apologies for my ignorance

Regards

 

[Mike375]

This query is actually not fed off any previous query results. The SQL I pasted earlier in this thread is directly attacking the tables with no pre-reqs.

I will look through what you have posted, my only concern is that what you have posted is based around the thought of previous queries.

Apologies for my ignorance

Regards

Just look at this from your post at the start:

I appear to have two queries that if the result of either query is null, then none of the queries for the report run and the whole report comes out blank. If I remove the offending query and rerun the report, everything runs as I would expect.

I have been thinking in terms of a null field but I see now you are referring to a query not returning any records.

You mentioned two things:

1) If the queries are null the lot does not work

2) if you remove the queries then the rest works.

When the queries return no records and you remove them so that the rest works are the results you get what you require. If so your answer might be to set up that if the record count from these two queries is 0 thenyou run a set of of your queries where these two queries are not invloved.

In other words if these two queries return records then queries 1, 2, 3, 4 etc are involved but if these two queries do not return records then queries 3, 4, 5 etc run.

Mike

 

[Ice Rhino]

I am totally confused by this now. I think I shall just cancel what I am trying to do and find another way. I appreciate all the efforts made by all but I can't get across what I am trying to do, or I can't find the right words to describe it.

I thank you all for your help and look forward to the next problem and you finding it resolution for me.

Sincerest Thanks and apologises for giving up

 

[Ice Rhino]

In a flash of inspiration, a solution came to me. I now have a count sub query that counts the results of the the src query and if it finds nothign in there it returns a value of 0 which is fine. I have added this to the report and all is now well and perfect.

Thanks for all your support in this and apologies for any frustration that may have been caused.

Regards