Solved

MS access 2010

Posted on 2013-01-15
6
326 Views
Last Modified: 2013-02-23
I have build a running sum column on the report. I want to create a formula in group footer where I can get the last record of that group in the group footer. Like for each day whater is the last record it should show on the group footer. Also for another report if I want to filter only negative records, what formula I should use for it. thanks
0
Comment
Question by:snhandle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38781478
You can use dmax function. Something like (if GroupID is numeric):
=dmax("[RecordNUM","tblrecords","[GroupID]=" & Me.GroupID)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38782065
If you place a Max function as the control source of a textbox in the Group Footer ( not report footer) section of your report, this should be adequate to get what you're looking for:

= Max([IDField])

Open in new window

<< Also for another report if I want to filter only negative records, what formula I should use for it >>

Revise the SQL or your report's filter to be something like this:

SELECT * 
FROM YourTable
WHERE YourField < 0

Open in new window


Or from the Query design grid, just add this to the criteria box of the field you want to restrict to negative numbers:

< 0

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38782098
To get the value of the Running Sum to show up in the footer, I think you are going to have to do something like:

In the Detail_Format or Detail_Print event, store the value of that control to a variable declared in the reports general declarations. It has been a while since I have done this, so I cannot remember which of those events works best.

Then, in the Format or Print event of the footer, set the value of the unbound control in that section to that of the variable.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:snhandle
ID: 38785552
I mean I want to show the negative amount on the report in Running Sum field. Below is exampe of runningsum field.
10
15
-12
14
-66

I want to show only on the report negative amount -12 and -66 not the positive one. The positive amount should be on the report but not visible. How I can hide the positive amount?
0
 
LVL 40

Expert Comment

by:als315
ID: 38785818
You can use conditional format, for example. May be you can upload sample db with this report and some dummy data?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38856241
<Also for another report if I want to filter only negative records, what formula I should use for it. thanks >
This is really a separate question, not related to the first.
(Besides, you never stated what you wanted if the value was Zero...)

This site is functions on a "One question per post" basis...

As you can see, an expert is willing to assist, but a question really need to have only one distinct "answer"
Database84.mdb
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question