Syntax to display specific calculated fields

[Sharky II]

Hi guys

I have a search results form, and would like to show some fields which are calculated based on the results shown.

For example, i have a list of transactions, some are labelled 'incoming' and some are 'outgoing'. I can easily calculate the total by setting the control source as "=Sum([TotalTransactionCost])". Or i can calculate the number of total results by creating a field and setting the control source to =Count(*).

But i would like to show some calculated fields based on the 'type' of transaction - incoming or outgoing for example.

How can i create a field which calculates based on the 'TransactionType' field? E.g. if TransactionType=Incoming then =Sum([TotalTransactionCost]) - and similarly one for outgoing?

Or perhaps a count of all incoming and all outgoing. As i mentioned, these are simply fields in the results, which are displayed in the results.

The control source box opens up an expression editor and not a query box... not sure how i would do it either way anyways!

Apologies for the basic question. I've attached a screenshot... obviously at the moment the incoming/outgoing are the same as i don't know how to do it (also, the fields in the footer of the subform would be hidden once i fix the problem!)

Thanks!

 

[JamesMcS]

You could do this a couple of ways - firstly using DSum. Make this your text box's control source:

=Dsum("[Item Total]","[SourceTableName]","[Type]='Outgoing'")

Modify as needed. Second you could use sum and iif together:

=Sum(Iif([Type]="Outgoing",[Item Total],0))

 

[Sharky II]

Thanks very much! I'm gonna try this out now.

Thanks again!