Solved

MS access 2010

Posted on 2013-01-15
6
317 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 47

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

739 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