Criteria for an Expression Field

[kmkting]

Hi - i have a field that is an expression, "Total C" that adds up "Total A" field and "Total B" field. I want to write an expression in the "Total C" field that excludes any totals of ZERO.

If i put <>0 or other such things in the expression "total C" it asks for parameters of "total A" field and "Total B" field which i do not want. It does not appear when running this query normally.

is there a way to solve this?

Total A Total B Total C
# # Expression {Total A + Total B}

Thanks in advance!

 

[kmkting]

The above SQL view is for the query i am trying to run. For the first posts i used "Total A" to be more generalized rather than explaining everything.


In this case, "Total A" is equal to "APPROVED_CREDIT_CARD"
and
"Total B" is equal to "APPROVED_BILL_ME"

Please ignore the visits.

Total C is [SumOfAPPROVED_CREDIT_CARD]+[SumOfAPPROVED_BILL_ME] AS Expr1

 

[Joe4]

Did you see my previous post?

I believe splitting it into two queries will allow you to accomplish what you are after.

 

[kmkting]

To split into two queries...

how do i do so?

Am i going to use the existing query to calculate the Total A + Total B...
and then create a second query off THAT query to exclude a sum of 0?

If this is true, would i still need to link the query to the table that feeds the data?

 

[Joe4]

You can create queries based on tables or other queries. I often break down complicated queries into smaller, more manageable queries. You do NOT need to connect to the underlying table, connecting to the query does that automatically (they nest).

What I would is take the query you have now that calculates your Total A and Total B. Remove the expression that adds Total A to Total B.

Now create a new query, using the first query as your source. Add all the fields you want, and add the expression that adds Total A to Total B, and then put the criteria on this calculated field.

 

[kmkting]

Thank you so much, i believe that this has worked. Many thanks.

 

[Joe4]

Glad we got it to work out for you!

One of the reason why I like to break complex queries up like that is that it also makes them a lot easier to debug when something isn't working out right.