Nz and Unbound Reports

[Jerry Stoner]

Been a long time since I've posted. Been playing in SQL Server 2000 land. Anyway...
I've got a main report (no record source) with 3 unbound subreports in the hidden detail section which feed multiple text boxes with about a hundred calculations. All works fine except when one of the three queries that populate the subreports has no data. I of course get #error#. Tried Nz but still get an #error# because again of course there is no null but #error#. Nz won't work in the query either since there is no data. How do I get a 0 in the (numeric) fields of my subreports?

 

[AnotherWayne]

Jerry,

Make your queries union queries and retrieve a row of
constants, or nulls from the second query.

AnotherWayne

 

[Jerry Stoner]

Thanks I'll give it a try.

 

[Jerry Stoner]

Well a Union wont work because the are a different # of fields.

=IIf([TimeInMin]>0,[TimeInMin],0) Returns correctly in the detail section of my subreports but

=Sum(IIf([TimeInMin]>0,[TimeInMin],0)) in the footer is showing ERROR?
Any ideas?

 

[AnotherWayne]

Jerry,

You can select any number or kind of constants:

Union
Select 'Garbage', '', 0, 'N/A'
From YourTable

Wayne

 

[Jerry Stoner]

Thanks Wayne but a union query is not needed.Yes I did try it but Im not selecting like data and they need to be in their own text fields for the reports. Perhaps I complicated things with my first explanation.
I'm doing a for the period quality report which collects data from several queries. The only thing the data has in common is the data and a CellID. These are parameters in the queries. One query has a QTY produced and will always have data . The others are TimeInMinutes and Qty (defects)and may not have data. When there is no data for one of the queries the result is error.

Lets look at qryOOEAvail only, the SQL is:

SELECT qryMachShopDailySub.TimeInMin
FROM qryCell INNER JOIN (qryMachShopDailySub INNER JOIN qryMachShopDaily ON qryMachShopDailySub.MachShopDailyID = qryMachShopDaily.MachShopDailyID) ON qryCell.CellID = qryMachShopDailySub.CellID
WHERE (((qryMachShopDaily.ProdDate)>=[Forms]![frmMachShopProdRep1]![txtStartDate] And (qryMachShopDaily.ProdDate)<=[Forms]![frmMachShopProdRep1]![txtEndDate]) AND ((qryMachShopDailySub.CellID)=[Forms]![frmMachShopProdRep1]![cboCell]) AND ((qryMachShopDailySub.CauseCodeID)>0));

When there is no data in TimeInMin =IIf([TimeInMin]>0,[TimeInMin],0) will return a 0 but only in the reports detail section. I cant seem to get it to work in the footer. =Sum([TimeInMinutes]) works if there is data but =Sum(IIf([TimeInMin]>0,[TimeInMin],0)) fails every time. Nz also returns an error.

So, how do I account for no data in an individual query and get a zero returned?

 

[Jerry Stoner]

bump

 

[Jerry Stoner]

I know that should work but it does not. Not in the query, the TimeInMin field in the detail section or in the footer?? Just dont get it and the db is way too big to post.

Arrgh!!!

 

[Jerry Stoner]

Thanks Pat. Though that was not the problem your response did lead me to an answer. Simply do the sum in the query instead of the report footer. =(Nz([TimeInMin], 0)) then worked fine in the footer while for some reason =Sum(Nz([TimeInMin], 0)) still didn't. Anyway it works now. Thanks again for the nudge.
Jerry