UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access Nzformula., Access 2016    
 
   
Makya
post Jan 21 2020, 06:30 AM
Post#1



Posts: 23
Joined: 11-July 19



Hi, (Newbe with formulas.)

I Have a Report with Debit, Credit and Balance columns.

Then in the Footer I have Totals for all 3 columns.

Then I have a Previous Balance Formula also in the footer. I need a Closing Balance please.

Balance: =Sum([BALANCE])

Previous Balance: =Nz(DSum("[BALANCE] ","SALESAC2","ID=forms!frmLedger2!ID and date<forms!frmLedger2!StartDate"),0)

How do I get a Grand Total of the "Balance" and "Previous Balance" Fields?

Tried a few different ways but get errors all the time. Might be a very easy solution.
Go to the top of the page
 
June7
post Jan 21 2020, 06:51 AM
Post#2



Posts: 1,387
Joined: 25-January 16
From: The Great Land


Have you tried subtraction expression referencing the two textboxes?

Post what you have attempted.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
BruceM
post Jan 21 2020, 08:26 AM
Post#3


UtterAccess VIP
Posts: 8,109
Joined: 24-May 10
From: Downeast Maine


What is the name of the form in which the expression is to be used?

I think you need to put the values outside the quotes:

"ID=" & forms!frmLedger2!ID

This assumes ID is numeric.

In the second part of the criteria, if date is a field you should change the name, but if you are going to use it you need to surround it with square brackets to avoid Access understanding it as the Date function. If it is a date field you need date delimiters (#):

" and datefield < #" & forms!frmLedger2!StartDate & "#"

Also, will all users have mm/dd/yyyy (or m/d/yy, etc.) as their regional date format? If not, you may need to convert the StartDate value into a format Access will interpret properly.
Go to the top of the page
 
Makya
post Jan 22 2020, 01:37 PM
Post#4



Posts: 23
Joined: 11-July 19



Hi

The Balance: =Sum([BALANCE]) is correct and working fine so is
the Previous Balance: =Nz(DSum("[BALANCE] ","SALESAC2","ID=forms!frmLedger2!ID and date<forms!frmLedger2!StartDate"),0)

The Balance is for a column on my report (Start and End Date Report)

The Previous Balance Formula is for the same Balance column but only until the "StartDate" of the Current Report. In other words: Opening Balance for the Balance Column.

The current report only gives me The Balance Total for the Selected Date Range.

What I need is a Closing Balance for the Balance column.

Therefor I need to Sum the Previous Balance and the Balance Totals.

Balance Text box name: txtSumBalance
Previous Balance Text Box Name: PBalance

Hope this is clear. (Both in Footer of Report.)
This post has been edited by Makya: Jan 22 2020, 01:57 PM
Go to the top of the page
 
Makya
post Jan 22 2020, 02:03 PM
Post#5



Posts: 23
Joined: 11-July 19



=Sum([txtSumBalance]+[PBALANCE])
error: Enter Parameter Value - txtSum Balance

If I sum the formulas for the 2 Boxes
error: Cannot have aggregate function in expression.

=Sum([Balance]+Nz(DSum("[BALANCE] ","SALESAC2","ID=forms!frmLedger2!ID and date<forms!frmLedger2!StartDate"),0))
I get a wrong amount. Totals not calculating correctly.



As I said. Newbe to functions
This post has been edited by Makya: Jan 22 2020, 02:11 PM
Go to the top of the page
 
June7
post Jan 22 2020, 02:20 PM
Post#6



Posts: 1,387
Joined: 25-January 16
From: The Great Land


Try changing paren location and concatenate control references. Assume date is a field name so enclose in [] because date is a reserved word. Should not use reserved words as names for anything.

=Sum([Balance])+Nz(DSum("[BALANCE] ", "SALESAC2", "ID=" & forms!frmLedger2!ID & " and [date]<#" & forms!frmLedger2!StartDate & "#"),0)

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Makya
post Jan 22 2020, 03:31 PM
Post#7



Posts: 23
Joined: 11-July 19



Thank you so much. I knew it had to be something small I could not figure out.

Works perfectly.
This post has been edited by Makya: Jan 22 2020, 03:31 PM
Go to the top of the page
 
WaseemUzair
post Feb 21 2020, 04:40 PM
Post#8



Posts: 1
Joined: 21-February 20



please can you help me in this formula. i tried too much to get previous balance but i failed.

thanks in advance
Go to the top of the page
 
June7
post Feb 21 2020, 05:13 PM
Post#9



Posts: 1,387
Joined: 25-January 16
From: The Great Land


Help you with what formula? You should start your own thread.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Bullschmidt
post Mar 14 2020, 11:02 PM
Post#10



Posts: 54
Joined: 8-May 19
From: USA


QUOTE
please can you help me in this formula. i tried too much to get previous balance but i failed.


Well the OP said that this worked fine for him:
Previous Balance: =Nz(DSum("[BALANCE] ","SALESAC2","ID=forms!frmLedger2!ID and date<forms!frmLedger2!StartDate"),0)

So maybe you can also use the DSum() function but with different parameters. And the reason it is wrapped in the Nz() function is so that if the answer is Null that it gets changed to be 0 instead.

--------------------
J. Paul Schmidt - Freelance Web and Database Developer
Sample Database on the Web
Sample Access Database

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th May 2020 - 09:37 PM