# Criteria for an Expression Field

#### kmkting

##### New Member
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}

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hello. Can you please provide some clarification as to what you're needing to do? ...like some sample data. Not wanting 0's in an average I could understand, but not sure I get how/why it is effecting your sum.

okay here it goes - i have a table that feeds data to run the query which is run for a specific date (time period).

[Date] [Total A] [Total B] [expression: Total A + Total B]
9/5/06 2 5 7
9/5/06 0 2 2
9/5/06 3 0 3
9/5/06 0 0 0

in this example, i would not want the last entry to appear when running this query. i DO want the two above it to show up.

so i didnt know if there was criteria i could include under the [expression: A + B] field, or write another expression field - i really don't know.

hopefully this makes it easier and i'd appreciate any input. Thanks!

sorry - i didn't preview my post and the data got distorted.

i wanted each number to go under each heading

[Date] [Total A] [Total B] [expression: Total A + Total B]
9/5/06 --- 2 ---------5 ---------------7
9/5/06 --- 0 ---------2 ---------------2
9/5/06 --- 3 ---------0 ---------------3
9/5/06 --- 0 ---------0 ---------------0

so the 2 + 5 gives 7, etc.

That clears it up for me. I had completely misunderstood your question. I believe this could be easily solved by making your criteria be A <> 0 AND B <> 0.

hth,
Giacomo

I don't understand why you are having problems using "<>0" as your Criteria under your expression. It should work.

If you are being prompted for parameters, the question I would ask is this query based on any parameter queries? If you have a parameter query somewhere along the way, the parameter prompts will show up in this query also.

I just had another thought. Are the Total A and Total B fields also calculations in this same query? If so, I can see why you would be getting prompts.

When the query is run regularily there are no prompts...

The Total A and Total B fields are "SUM" fields (as opposed to Group By, Avg, Min, etc)

When i try putting in the <>0 i still get the parameter pop-ups for Total A and Total B.

please post your sql. Go to View >> SQL View in the query editor.

It sounds like you are working with an Aggregate Query. Trying to do additional calculations in an Aggregate Query can be tricky.

I prefer to break it up into 2 queries, one for the Aggregate Query (to Sum up A and B), and then a second query based on your Aggregate Query which does your addition and uses your criteria.

Here is the SQL view:

SELECT REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID, [RR - Club Abbrev].[Club Abbrev], Sum(REPORT_REPORT_NM_DAILY_PROMOID.APPROVED_CREDIT_CARD) AS SumOfAPPROVED_CREDIT_CARD, Sum(REPORT_REPORT_NM_DAILY_PROMOID.APPROVED_BILL_ME) AS SumOfAPPROVED_BILL_ME, Sum(REPORT_REPORT_NM_DAILY_PROMOID.TOTAL_VISITS) AS SumOfTOTAL_VISITS, [SumOfAPPROVED_CREDIT_CARD]+[SumOfAPPROVED_BILL_ME] AS Expr1, [Expr1]/[SumOfTOTAL_VISITS] AS Expr2, REPORT_REPORT_NM_DAILY_PROMOID.START_DATE
FROM [RR - Club Abbrev] INNER JOIN REPORT_REPORT_NM_DAILY_PROMOID ON [RR - Club Abbrev].[Club Code] = REPORT_REPORT_NM_DAILY_PROMOID.CLUB_CODE
GROUP BY REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID, [RR - Club Abbrev].[Club Abbrev], REPORT_REPORT_NM_DAILY_PROMOID.START_DATE, REPORT_REPORT_NM_DAILY_PROMOID.CLUB_CODE
HAVING (((REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID) Like "034*") AND ((REPORT_REPORT_NM_DAILY_PROMOID.START_DATE)>=#8/27/2006# And (REPORT_REPORT_NM_DAILY_PROMOID.START_DATE)<=#9/2/2006#));

Replies
15
Views
545
Replies
9
Views
839
Replies
7
Views
204
Replies
1
Views
393
Replies
12
Views
979

1,219,935
Messages
6,151,065
Members
451,006
Latest member
dhinze84

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back