Sub Reports and #errors

[Chrishill]

Hello,

I have crated a simple database to organize "orders", "shipped" and "pending"

Problem:
The pending report does not work if i have not shipped against the order.

the report is grouped by product then "order" and has a sub report calling up the shipped against value for each order. the report calculte "order" - "shipped" to give me "pending"

If there has been a shipment againt the order everything works. if no shipment has been made against the order i get a #ERROR in the PENDING text box. I know this is becuase there is no variable to lookup.

I have tried a couple of IIF statements that have not worked.
can I use something like:

IIF([Ship]<>0, [order]-[ship],[order])

where - [ship] is text box that totals from the shipped subreport. it will be either be a number or a #error. when it is a number i want to calculate (order - ship), when it is a #error i wnat it to be [order]

can you tell me the proper iif statement to do this or any other way to solve the problem.

 

[Liv Manto]

nz function.

 

[Chrishill]

thank - you for the reply

i tried the Nz function, but i still got an #error - there is no variable to null.

what i have had the best luck with is trying to differentiate between a number and a #error in an IIF statement??? still not working - where would you use the Nz??

 

[Liv Manto]

IS this how you did it?

IIF(nz([Ship],0) <>0, nz([order], 0) -nz([ship], 0) ,nz([order],0))

 

[Chrishill]

thanks you for the syntax - i tried it and still get #error

when an order has not had any thing shipped againt it the subreport is not there. So when i ask it to compute a value I get an #error. I think i need to make the sub report invsible so no #error is returned, but i do not know how to do that.

any other ideas - my problem stil exists

 

[Liv Manto]

Is the report's recordsource based on a query?

where are you putting the formula?

 

[Chrishill]

Query
In the control source

I have got it to work using VBA I now hide the subreport if no data is availible and display "nothing shipped" otherwise I show the total shipped values.