Criteria for an Expression Field

kmkting

New Member
Joined
Jul 10, 2006
Messages
41
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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.
 

kmkting

New Member
Joined
Jul 10, 2006
Messages
41
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!
 

kmkting

New Member
Joined
Jul 10, 2006
Messages
41
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.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,945
Office Version
  1. 365
Platform
  1. Windows
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.
 

kmkting

New Member
Joined
Jul 10, 2006
Messages
41

ADVERTISEMENT

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.

Let me know if more information is needed.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
please post your sql. Go to View >> SQL View in the query editor.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,945
Office Version
  1. 365
Platform
  1. Windows
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.
 

kmkting

New Member
Joined
Jul 10, 2006
Messages
41
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#));
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top