Limit report to only amounts greater than an absolute value

In the footer section of the report, I have the following calculation

=Sum(nz([dateb])-nz([datea]))

I would like for the report to only show those values > |100,000| (based on 
the above calculation)

How can I do that?
0
Utf
5/16/2007 8:41:01 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
882 Views

Similar Articles

[PageSpeed] 5

Base your report on a query and set the condition you want in the criteria 
of an appropriate field.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com



"Jim McC" <JimMcC@discussions.microsoft.com> wrote in message 
news:F21DABE2-B33D-4B59-B7EB-23E61E28F3D6@microsoft.com...
> In the footer section of the report, I have the following calculation
>
> =Sum(nz([dateb])-nz([datea]))
>
> I would like for the report to only show those values > |100,000| (based 
> on
> the above calculation)
>
> How can I do that? 


0
Steve
5/17/2007 2:14:43 AM
Duane,

In this case "Only Show" means

 In the footer section of the report, If the difference between 
=Sum(nz([dateb])-nz([datea])) is greater than |100,000| show the entire line. 
 If the Difference is less than |100,000| take that line off the report 
completely (i.e. that line basically disappears from the report.

Thanks 


"Duane Hookom" wrote:

> Your use of "only show" is confusing to me.
> 
> If you just want to sum the values where difference between DateB and DateA 
> > 100000 then try something like:
> 
> =Sum( Abs((nz([dateb],0)-nz([datea],0))>100000 ) * 
> (nz([dateb],0)-nz([datea],0)) )
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Jim McC" wrote:
> 
> > In the footer section of the report, I have the following calculation
> > 
> > =Sum(nz([dateb])-nz([datea]))
> > 
> > I would like for the report to only show those values > |100,000| (based on 
> > the above calculation)
> > 
> > How can I do that?
0
Utf
5/17/2007 1:50:00 PM
Reply:

Similar Artilces:

freeze values
XL2000 I made a sheet that helps me do estimates based on several different products and lengths. everything works perfectly but one little detail When I input some sizes, excel does its thing, and give me all the information in six separate cells. Problem is, in six cells just below I need to estimate basically the same product but with different sizes when I change the sizes all of the information changes on the first six cells Question is there away to freeze the first six cell information before I change the size for the next size cells Hi Alan AFAIK only be "removing&qu...

test a range for value....
Hi, I have a column of dates, formatted as date dd/mm/yyyy. I would like to have a formula in one cell that will evaluate the entire range to check if all the dates are within a given year. If A1 is 01/01/2008, I can put =year(a1)=2008 in B1 and return a true or false, but I can't seem to get it to work for a range rather than just an individual cell. Any help would be greatly appreciated. TIA. Jim Assuming your dates are in column A, with the search date 01/01/2008 in B1. This formula will count how many dates are not in the same year as B1: =3DSUMPRODUCT(--(YEAR(A1:A1000)=3DYEAR(B...

Sumif returning incorrect value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have data in columns c to f and rows 2 to 20 <br> I also have data in a2 to a20. <br> I am trying to sum the data in s2 to f20 when the row matches my criteria in a2 to a 20. The formula that I am using is <br><br>sumif(a2:a20,&quot;=12&quot;,c2:f20) <br><br>the result will only sum the values in column c. <br><br>Does anyone know what the problem is? <br><br>Thanks <br><br>Allan SUMIF() has various limitations. this will work,...

How to bold daily max values
I have one year of hourly data in one column and the date in another column. I'd like to write some excel functions to bold the daily max values. Any help is greatly appreciated! Ginger Conditional formatting would do this, but I don't understand the data. Can you post some sample data? -- HTH Bob Phillips "Ginger" <Ginger@discussions.microsoft.com> wrote in message news:34230977-525A-476E-A29F-AA27144E1E10@microsoft.com... > I have one year of hourly data in one column and the date in another column. > I'd like to write some excel functions to bold t...

not draw zero values in a line chart
Hi I’m trying to create a line chart but I don´t want the zero values or empty cells to be draw. the line should be inetrpolated from de last value to the next . Two parts To skip blank cells, go to Tools-Options->Chart. Select Plot empty cells as not plotted. (Note cell must be empty, not a formula returning "") Zero values are a little trickier. You'll need to either remove them (Find and Replace) or if they are a formula, have the formula return the #N/A error. Example: =IF(A1=0,NA(),A1) XL charts ignore the #N/A error as they are, by definition, 'not appli...

Compare records in multiple sheet -> report
Hi! I have a workbook consisting of ten sheets. To simplify my question let�s say that the three first columns of every sheet denotes the spatial coordinates x,y,z and the fourth column is a scalar value. Some x,y,z-triplets exist in all 10 sheets, some exists in only a few sheets, if the triplet exists, then also the scalar value of the 4th column exists. What I would like to do is to find all unique x,y,z-triplets and show them in the first column of a new sheet. In columns 2-11, I would like to show the scalar value(from the corresponding x,y,z-triplet of course) in column 4 in s...

How to create web-based reports from RMS database.
The reports available in RMS is not specific enough for my apparel retail store. I need sell the through rate, average sale, sales/sf, return rate, discount analysis, all broken down by date, week, month, year, category, supplier, and then compare against last year's numbers. I want to create my own web-based reports accessing RMS's SQL database. Does anyone have a template of data access web page so all I have to do is change the SQL statement in getting to the right database table? mas wrote: > The reports available in RMS is not specific enough for my apparel retail > ...

Error while trying to access reports
Hi all, I have a user who is Admin and gets this error when she clicks on the report icon: Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the r...

Copying values from place to place
Hello community: Is it possible say, to type a value in a textbox in one form (Form A), automatically copy that value, then open another form( Form B) and automatically paste that value into another textbox in Form B? Is this possible? Thank you for your help. -Gabriel M. If you open Form B from Form A you can use the OpenArgs of the OpenForm command line to pass a value to Form B. docmd.OpenForm "FormName",,,,,,Me.TextBoxName On the OnLoad event of the Form B you can use set the value in the text box If not IsNull(Me.OpenArgs) Then Me.TextBox = Me.OpenArgs End If -- ...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

Infopath w/ manually entered values in drop-down and qry results
I had originally posted this elsewhere, but was told this forum is the appropariate plase. I have an Infopath form with a drop-down listbox, that is poulated with manually-entered values. I choose a value, submit the updated data, and it does put the correct value in the SQL 2005 database. However, the next time I query the data, the value in the drop-down list box is the default value for the list-box, not the value from the database, which is misleading. I would have expected the drop-down listbox to display the value from the database instead. Thanks. On Tue, 4 Sep 2007, in...

Limited User Account
I am thinking of buying Microsoft Money Plus 2008 to use on a computer with Windows XP. I read in the user guide that I must install it with an administrator account, which I would expect. After it is installed, can I import data from Quicken 2006 and use MS Money Plus from a limited user account? The reason I ask is I do all my routine work with a limited user account and only use the administrator account when absolutely necessary. Gerry In microsoft.public.money, Geber wrote: >I am thinking of buying Microsoft Money Plus 2008 to use on a computer >with Windows XP. I read in the...

Outlook 2003 contact limit
Hi I have a excel spred sheet with 1300 contacts in it, i have some problems importing them into Outlook 2003 a lot of the contacts are not imported Is there a limit on how many contacts you can have? or am i doing something wrong Here is what i have done I have exported my contacts to a spread sheet and then added a lot of contacts and then tryed to import it again without success You are far below what the item count limit is. Outlook 97-2002 Personal folders 65,000 items per folder (max file size for pst is 2GB) Personal Folders (unicode) personal folders Unknown limit (artificial lim...

Average & eliminating zero value Need HELP !! PLEASE
In cell C4 im calculating average hours for D5:D65 of all employees In cells Z5:Z65 If L is entered means laid off In cells AB5:AB65 IS AVERAGE OF ALL EMPOLYEES HOURS in cells D5:D65 if L is entered in Z Column In cells D5:D65 IS all employees hours FORMULA FOR C4 IS {=AVERAGE(IF(ISNUMBER(D5:D65),IF(D5:D65>0,D5:D65,0)))} Formula for cells AB5:AB65 IS =IF(Z5="L",R4,"") Formula for D5:D65 IS =IF(Z5>0,0,IF(B5="","",AA5+SUM(E5:J5)*1.5+K5*2)) I want to average all employees hours except if L is entered in Z Column so say 65 empl...

Reports not availble on ssms
Hello Gurus, Here is a situation I have versions of SQL from 2000 to 2008 in both the 2005 and 2008 version of ssms I can not see the report section when right clicking on a database in SSMS. However I can view these reports when I RDC into the server using the exact same windows credentials?? Is there something missing on my client tool that would cause this? or What?? help! Wisord *** Sent via Developersdex http://www.developersdex.com *** ...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

Report Server Error 09-28-07
After uninstalling MSCRM from my server, I tried installing it again. This has led me to the problem of the report server. I'm using the default server name http://<servername>/reportserver and it's still not realizing that it is actually there and fully running. I've checked the Services section of my server and also checked the individual server status. Both are running within normal parameters. Before the uninstall, the report server was running properly and CRM recognised that it was actually there. Any ideas? ....reed Is this SQL Server 2000 or 2005 SRS? 1. Fo...

not repeating text boxes in reports with columns
Hello, I am trying to create a report with columns without repeating certain text boxes. Here is an example of what I would like to create: [Date] "Month1" [Date] "Month2" [Date] "Month3" [Product] "Product1": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product2": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product3": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product4": [q...

Access SSRS Throug SSMS not Report Manager
Without giving someone membership into the "BUILTIN\Administrators" group, how would one enable a user to be able to access SSRS through SSMS? SSMS >> Connect >> Reporting Services >> {Server Name} I'm currently getting the error below: TITLE: Connect to Server ------------------------------ Cannot connect to {servername}. (removed the server name) ------------------------------ ADDITIONAL INFORMATION: A connection to the computer cannot be established. (Microsoft.SqlServer.Management.UI.RSClient) ------------------------------ Acces...

Crystal Reports help
We are currently using GP 8.0 and Crystal Reports 10. We will be running most of our reports through Crystal because you can do so much more than with the Report Writer. I am looking for a good reference book for Crystal 10 (this is the first version I have ever used) any suggestions? You can get books on Crystal 10 in any large bookseller. One thing you won't get is how to use Crystal with Great Plains. There was some training materials available. You will want to consult the Great Plains SDK for help on the tables to use. JO wrote: > We are currently using GP 8.0 and Crystal R...

script error running reports from home pages, why??
I'm not a GP user/expert, we recently installed GP2010. Since then one user has said, "When I try to run reports from the home pages, I am getting this script error and cannot run any reports." The error is: Line 47 Char 5 Error The system cannot find the file specified Code 0 URL: file:///C:/Users/username/AppData/Local/Temp/tmp/AE8.tmp I have received differing responses to this issue: 1) enable the Run command on the Start menu of our XenApp server for the regular domain users, which I view as a big security risk 2) "the UserData folder does not exist in the %ap...

Find a value in cells
Hi, all, I have something like this : a 1 b 4 c 5 d 3 I'm doing a max function in the number column and it returns me '5' But, after that, I want to know the letter which corresponds to the max number How can I do that? Thanks Nic -- nicgendron ------------------------------------------------------------------------ nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151 View this thread: http://www.excelforum.com/showthread.php?threadid=386417 Let's say that...

Logical Filtering based on Value
I've been using this formula to sort some values and return results if( or( and( b9<>"", isnumber( find( Left(b9,1 ,"AWFUY") ) ), isnumber( find("SHORT",g9 ) ), sum( countif(c9,{"BOG","BLM","CMO"}) ) ) ) ) ,i9-0.01,j9 ) --I used ALT-ENTER between each formula so as to see thing clearly Column A values can be "B01" Column B values can be "BRN" Columns I & J are values, one being lowest the other being highest allowed. On another sheet I...

How to force the user to fill in values for yes/no fields ?
Hi ! I'm using MS Access 2003. I have a table with a number of yes/no fields. When a new record is started, I want the user to explicitly specify a value for each of these fields, e.g. by choosing one of two radio buttons associated with the field. Now, it was no problem for me to arrange an option group with two suitable radio buttons for each of these fields. The user can then choose explicitly between yes and no. However, all fields seem to have implicit values immediately after a new record has been started. Therefore, the new record can be stored in the db without the user making...

Counting in a Report
I have the following entered for the Control Source in a group header text box on my report: =Abs(Sum([CountOfGoal])) Now in the footer of my report I want to add the result of those text boxes in another text box. What should I enter for the Control Source of that text box? Thanks, CEV "CEV" <chadv@advancebkg.com> wrote in message <evr8e50dpb@enews1.newsguy.com>: > I have the following entered for the Control Source in a group header > text box on my report: > > =Abs(Sum([CountOfGoal])) > > Now in the footer of my report I want to add the re...