Solved Expression text formula (1 Viewer)

mhakim

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
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
 

Attachments

  • sumdebitplminus sumcreditpl.PNG
    sumdebitplminus sumcreditpl.PNG
    59.7 KB · Views: 198

oleronesoftwares

Passionate Learner
Local time
Today, 06:29
Joined
Sep 22, 2014
Messages
1,159
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.
 

Attachments

  • report final output .PNG
    report final output .PNG
    8.5 KB · Views: 198
  • reportwizard8.PNG
    reportwizard8.PNG
    8.5 KB · Views: 191
  • reportwizard7.PNG
    reportwizard7.PNG
    28.5 KB · Views: 197
  • reportwizard6.PNG
    reportwizard6.PNG
    42.8 KB · Views: 192
  • reportwizard5.PNG
    reportwizard5.PNG
    16.4 KB · Views: 149
  • reportwizard4.PNG
    reportwizard4.PNG
    28.2 KB · Views: 191
  • reportwizard3.PNG
    reportwizard3.PNG
    34.2 KB · Views: 187
  • report wizard2.PNG
    report wizard2.PNG
    31 KB · Views: 191
  • report wizard1.PNG
    report wizard1.PNG
    25.1 KB · Views: 174
  • querybaldesign.PNG
    querybaldesign.PNG
    14.8 KB · Views: 195

strive4peace

AWF VIP
Local time
Today, 08:29
Joined
Apr 3, 2020
Messages
1,003
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

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
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
 

mhakim

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
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

Passionate Learner
Local time
Today, 06:29
Joined
Sep 22, 2014
Messages
1,159
[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

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
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
 

Attachments

  • query design.PNG
    query design.PNG
    24.5 KB · Views: 177
  • sumdebitplminus sumcreditpl.PNG
    sumdebitplminus sumcreditpl.PNG
    59.7 KB · Views: 201

oleronesoftwares

Passionate Learner
Local time
Today, 06:29
Joined
Sep 22, 2014
Messages
1,159
=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

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
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

Member
Local time
Today, 16:29
Joined
Jan 25, 2021
Messages
66
i find the solution

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

Users who are viewing this thread

Top Bottom