Blank query result to a textBox

[edh]

Hi there. I have four separate queries each producing a value named "Sum of Price" for a particular category of product. they are embedded into a subform of a booking form for with Master & child fields set accordingly. on the booking form I have a text box I want to show the total of all the queries. I'm using code:

=[EventBookings Query subform].[Form]![Sum Of Price]+[BBQBookings Query subform].[Form]![Sum Of Price]+[CampBookings Query subform].[Form]![SumOfPrice]

Which works fine unless there are no values for one of the queries to work on leaving the query result blank in which case I get "#Type!" in the field. (If it's just one query feeding the textBox then I get "#Error")

Can I put a default value of zero onto the query somehow or maybe an If statement on the textbox...???? Thanks

 

[edh]

Hi again, just thought I'd tell anyone interested that I've got a solution to this problem. I tries Nz() in the text Box and in the query with no change. actually there was one change, when used in the query the error became #Size! (the result should have been 3 characters long and the box is plenty big enough...) but still no use to me. I ended up building the expression for the text box with an IIF(IsError(... at the beginning of each statement so it now looks like this:

=IIf(IsError([BBQBookings Query subform].[Form]![Sum Of Price]),0,
[BBQBookings Query subform].[Form]![Sum Of Price])+IIf(IsError([CookOutBookings
Query subform].[Form]![Sum Of Price]),0,[CookOutBookings Query subform].[Form]![Sum Of Price])+IIf(IsError([EventBookings Query subform].[Form]![Sum Of
Price]),0,[EventBookings

Kinna feel like I've cheaped out using this method. I'd prefer to be able to have it return 0 just on the error of a blank query and warn me of any other errors still. Anyone able to help here? Thanks

 

[pr2-eugin]

Hi Nz() is the way to go around.. Nz works in a way of simplified If that checks only one condition If the field is NULL.. so the syntax is

Nz( [I][B]field_that_you_are_checking[/B][/I] , [I][B]what_value_do_you_want_if_it_was_null[/B][/I] )

So, based on that.. try this code..

=Nz([EventBookings Query subform].[Form]![Sum Of Price],0)+Nz([BBQBookings Query subform].[Form]![Sum Of Price],0)+Nz([CampBookings Query subform].[Form]![SumOfPrice],0)
[COLOR=Green]'The above code will replace all NULL Values with 0 else the values will be added up.[/COLOR]

Let me know how it went on.. If I have got it wrong.. please let me know..