query problem

[iso96]

Hello,

I have a query which lists all values in a table which contain 'Y' these values are then all added together using sum. I then have another query which takes these values and adds them to some others from a different table.

my queries output when containing a 'Y'

field1 field2 field3 field4
Y 13 2 26


my queries output when containing no 'Y'

field1 field2 field3 field4


My problem is when there is no values to add eg all 'N' values in the table, therefore outputs an empty query the calculation in the next query doesnt work!

what i want my query to output when containing no 'Y'

field1 field2 field3 field4
0 0 0 0


i hope someone can help

many thanks

iso

 

[Brian1960]

In your query add for each result prior to summing a catch for null vales.
NZ(Variant, reslt if null)

 

[iso96]

thanks for replying

still cant get it to do what i want

Thanks


iso

 

[Brian1960]

post your queries and we can look at them.

 

[iso96]

thanks Brian, Ive partially sorted it.

I uesd :

IIf(IsNull([BOI]),"0",[BOI])

this works fine but i need it to set the other fields in the query to zero when the above one is zero.

can you help me with that

thanks

iso

 

[Brian1960]

This sets the field BO2 to zero if BO1 is zero,

IIF( IIf(IsNull([BOI]),0,[BOI])=0,0,[BO2] )

This sets the field BO3 to zero if BO2 is zero and BO2 to zero if BO1 is zero,

IIF( IIF( IIf(IsNull([BOI]),0,[BOI])=0,0,[BO2] )=0,0,[BO3] )

The " in the code my set the filed to a string so check if they are needed.