Solved

MS access 2010

Posted on 2013-01-15
6
295 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
6 Comments
 
LVL 39

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 39

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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