Links, links links.......Argh!!! 1

[DeviousDi]

Hi,

I know I'm missing something here, but don't appear to be able to see the wood from the trees!!

I have a dead basic report that shows, ff4 (dept), invoice number, invoice amount, amount_open, standard_queries.code and a formula called TrueAmount. The formula field is a thus:

if {standard_queries.code} = 'Q02' then 0 else {invoices.amount_open}

The problem being, and I'm pretty sure its to do with the links, it that, if in the formula field there is no query code, but an amount_open, the formula returns a 0 instead of the amount_open. e.g:

dept, invoice number, invoice amount, amount_open, standard_queries.code, TrueAmount

01, AB1, 528.00, 528.00, Q07, 528.00
05, CB1, 329.00, 329.00, ,

The formula should be showing 329.00 for the last line under 'TrueAmount', but isn't! It basically not returning the amountopen for all those invoices that don't have a query against them.

Here's the links, crystal generated, but altered by me:

SELECT "debtor_freefields"."ff4", "invoices"."invoice_number", "invoices"."invoice_date", "invoices"."amount_invoice", "invoices"."amount_open", "standard_queries"."code"
FROM ((("OnGuard"."dbo"."debtor_freefields" "debtor_freefields" INNER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "debtor_freefields"."debtor_id"="debtors"."id") LEFT OUTER JOIN "OnGuard"."dbo"."invoices" "invoices" ON "debtors"."id"="invoices"."debtor_id") LEFT OUTER JOIN "OnGuard"."dbo"."queries" "queries" ON "debtors"."id"="queries"."debtor_id") LEFT OUTER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id"

Here's the formula for the 'TrueAmount':

if {standard_queries.code} in ["Q08", "Q10", "10", "CC02", "CC03"] then 0 else {invoices.amount_open}

What am I missing?!

Thanks

Di

 

[pmax9999]

Try testing for Nulls, by amending the code for {@TrueAmount} to:

If  	IsNull({standard_queries.code})
Then 	{invoices.amount_open}
Else
If 	{standard_queries.code} = 'Q02' 
Then 	0 
Else 	{invoices.amount_open}

Cheers
Pete

 

[DeviousDi]

Pete,

I love you!!! Should have thought of that sooner, but sick of looking at the real version of the damn report!!!

Thank you!!!!

Have a nice weekend!!!

Di