Help with expression builder...

[scottmfitzge]

Hi Guys... I am pretty new to access, and i really need a bit of help. I am trying to place an expression into a query using expression builder. I have a column in my table called "TradeStatus" for each record (company) i want to count the number of trades where the trade status is "Completed" does anyone know what the expression would need to look like??

 

[khawar]

Use this expression
sum(iif([TradeStatus]="Completed",1,0))

 

[scottmfitzge]

Hi Thanks for that! I have placed this expression into my query but the following error is displayed You tried to execute a query that does not include the specified expression 'Entity(Long)' as part of an aggregate function" Entity(Long) is my customer name field. I have placed this onto the query in the first column and put your expression into the second column? Should i be doing this a different way? What i am trying to do is build a report that shows a summary of the data rather then a full list, so i thought i would create my totals and expressions in the query rather than in the report?

Thanks for all your help...

 

[khawar]

this expression has to be used in report

 

[khawar]

I misunderstood you can use aggregate query to get this

in the criterea row of query enter "completed" and count the column tradestatus

 

[scottmfitzge]

Ok thanks. I have added it into a report instead and it works perfectly.

I have a second question. I have a "Trade Amount" field. Next to the column that counts completed trades, i would like to add a column for Total Volume. So basically, the sum of trade amount, where trade status is equal to Completed.. how would i phrase this as an expression...

Once again thanks for all your help.. i am very new to all this (as you can probably tell)

 

[khawar]

Try this
sum(iif([TradeStatus]="Completed",[Trade Amount],0))

 

[scottmfitzge]

Thats fab.. works perfectly. Would you also happen to know how i can make the report run for a particular trade date range? e.g. last week and YTD?

 

[khawar]

Your can limit data in the query by filtering the date field
such as
where year(orderdate)=year(date())

 

[scottmfitzge]

Thanks for all your help...

How would i change the espression:
sum(iif([TradeStatus]="Completed",1,0))

If there was an additional condition that the "Product" must be equal to SPOT?

Thanks

 

[khawar]

sum(iif([TradeStatus]="Completed" and [Product]="Spot",1,0))

 

[scottmfitzge]

Excellent - It worked perfectly - Thanks for all of your help Khawar...

One very last question. In my calculations for percentages in my report, using the below expression, in some scenarios i get a return value of #Num!, which is correct, but instead of showing #Num1 i want to show either a 0 or a '-' any ideas how i can do this?

=[Text111]/[Text36]

Thanks

 

[khawar]

If you get this error because you are dividing by zero than you can change the expression as below

=iif([text36]=0,0,[Text111]/[text36])

 

[scottmfitzge]

Fantastic.... Works perfectly.. Thanks!