Solved Expression text formula

[mhakim]

i have query as PIC Attached

i need to put Expression in report text to allow me to get one value
= sum CreditPL Column - Sum DebitPL Column
= 1,108,530.52 - 461,769.16

will appear finally 646,761

 

[oleronesoftwares]

First, create the query in design view and save the query

Second, create a report using the report wizard and making the query the record source.

Look at the attachments and follow the steps.

 

[strive4peace]

it's not necessary to put the calculation in a query if you're going to use a report -- you can though. In either a group or report header or footer, you can use an expression like this in a textbox:

= Sum(CreditPL_fieldname) - Sum(DebitPL_fieldname)

 

[mhakim]

First, create the query in design view and save the query

Second, create a report using the report wizard and making the query the record source.

Look at the attachments and follow the steps.

i am so grateful for what you do
thanks alot

but i need the only expression final result
= sum CreditPL Column - Sum DebitPL Column
= 1,108,530.52 - 461,769.16

will appear finally 646,761 in text box

how to get NZ(SUMCreditPL)-NZ(SUMDebitPL)
BUT it does not work

 

[oleronesoftwares]

NZ(SUMCreditPL)-NZ(SUMDebitPL)

Put this instead NZ(Sum([credit]-[debit]))

my column names are credit and debit

You are putting the sum function wrongly

 

[mhakim]

Put this instead NZ(Sum([credit]-[debit]))

my column names are credit and debit

You are putting the sum function wrongly

Thanks for your effort
really appreciated

=Nz(Sum([IncomeStatement-T]![CreditPL]-[IncomeStatement-T]![DebitPL]))

I PUT THIS GIVE ME Error

[IncomeStatement-T] is the Query contain the two columns [CreditPL] and [DebitPL]

 

[oleronesoftwares]

[IncomeStatement-T] is the Query contain the two columns [CreditPL] and [DebitPL]

what is the name of the two columns, i.e for debit and credit. Can u share a screenshot of the design view of your query?

 

[mhakim]

what is the name of the two columns, i.e for debit and credit. Can u share a screenshot of the design view of your query?

Attached Design and also final view

 

[oleronesoftwares]

=Nz(Sum([IncomeStatement-T]![CreditPL]-[IncomeStatement-T]![DebitPL]))

Your columns are Tcredit and Tdebit, but you are using CreditPL and DebitPL.

CreditPL and DebitPL are alias similar to using expr1, they are not column names.

Change CreditPL to Tcredit and do same for DebitPL

Also where does Income statement come in, is it a table name, it looks more like a query name?

 

[mhakim]

So you say that i can not make any Expressions in reports on the query columns

because all values on Reports are coming from queries

i think there is a solution that we donot have

 

[mhakim]

i find the solution

=Nz(DSum("[CreditPL]","IncomeStatement-T","Sequence2 Like '4*'"),0)
-Nz(DSum("[DebitPL]","IncomeStatement-T","Sequence2 Like '5*'"),0)