Solved

MS access 2010

Posted on 2013-01-15
6
313 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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