Summing column that includes #Error values

I have asked this before but it's come back to haunt me.  I have a 
calculated value column in a query that contains some #Error values.  This 
generates an error when attempting to run a query which is a sum on that 
column.

How can I get around this - ie. make it ignore the #Error values when 
summing stuff? 


0
will
5/26/2007 1:06:46 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1099 Views

Similar Articles

[PageSpeed] 49

What is causing the errors?

MH

"will" <will@stream3.co.uk> wrote in message 
news:ekig4a5nHHA.3968@TK2MSFTNGP06.phx.gbl...
>I have asked this before but it's come back to haunt me.  I have a 
>calculated value column in a query that contains some #Error values.  This 
>generates an error when attempting to run a query which is a sum on that 
>column.
>
> How can I get around this - ie. make it ignore the #Error values when 
> summing stuff?
> 


0
MH
5/26/2007 2:24:34 PM
will wrote:

>I have asked this before but it's come back to haunt me.  I have a 
>calculated value column in a query that contains some #Error values.  This 
>generates an error when attempting to run a query which is a sum on that 
>column.
>
>How can I get around this - ie. make it ignore the #Error values when 
>summing stuff? 


Deal with the source of of the #Error instead of trying to
fidge it later.

You can test for an error value by using the IsError
function.  Possibly something like this will be useful:
	IIf(IsError(field), 0, field)

-- 
Marsh
MVP [MS Access]
0
Marshall
5/26/2007 2:54:50 PM
Reply:

Similar Artilces:

smtp error messege
i'm not able to send emails through my new email address (but it still works effectively for the old one). does it have something to do with the smtp # (it says 25- should that be change)? the smtp # 25 is referring to port 25 which the smtp protocol listens on. SMTP is your sending mail server. If you have a new email address on a different domain yet your smtp points to the old mail server, then yes, you should update it. If the email address is on the same domain as the existing email address, you may want to check other areas. Additional information is required. "ak" &...

Changing constants in a query to user input values
Access 2007 I have a table that contains (among other things) Start Date TimeDown End Date TimeUp All of these are Date/Time type Example data: Start Date TimeDown End Date TimeUp 16/3/10 02:00 16/3/10 04:00 16/3/10 06:00 16/3/10 09:00 16/3/10 19:00 16/3/10 20:30 16/3/10 23:00 17/3/10 01:00 17/3/10 03:00 17/3/10 04:00 17/3/10 08:45 ...

Error #-2147467259
I have a customer running RMS v2 SP3 on a Win XP Pro SP3 system that just started getting this error. The system has the RMS database locally with a fixed IP. They get the error multiple times a day and the systems locks completely. Up until a week ago they had no problems. Database connection lost, application will be closed. Error #-2147467259 We have: 1. reinstalled both RMS v2 sp3 and SQL Express 2005 2. In SQL Server 2005 Configuration Tools Client Protocols set Named Pipes as enabled and #1, set TCP/IP as enabled and #2 3. SQL Server 2005 Configuration Tools S...

Error when Running App with Scheduled Task
Error: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application. In the Main module; I have the following lines of code (VS 2008) which shows a minimized form; executes the code and closes the form. Sub Main() Dim custForm As New CustomerForm custForm .ShowDialog() End Sub On 2/4/2010 6:52 PM, Ryan wrote: > Error: Showing a modal dialog box or form when the application is not runni...

Problem install Office 2000 in Win XP SP 2
Dear all, Please help me how to solve Office 2000 installation error in WinXP SP2. I got the following error message : msiexec.exe has encountered a problem and needs to close. Installer terminated prematurely. Installer ended prematurely because of an error. Thanks. Chlaris I'd reccomend using a current version of Access sorry M$ subjects us all to 'planned obsolescence' "Chlaris" <chlarrissa_nospam@yahoo.com> wrote in message news:ujnbIFhtHHA.1208@TK2MSFTNGP05.phx.gbl... > Dear all, > > Please help me how to solve Office 2000 installation e...

Excel column headers #2
The column headers (in gray above my worksheet) are numbers instead of letters. I have Excel 2003. How can this be changed? It is very confusing when trying to write formulas. Happy new year This is one of your settings. Go into TOOLS > OPTIONS Then the GENERAL tab There's an option for R1C1 refernce style :-) Danny "Tara" wrote: > The column headers (in gray above my worksheet) are numbers instead of > letters. I have Excel 2003. How can this be changed? It is very confusing > when trying to write formulas. ...

InterOrg Replication problems / errors
If I want to replicate new data from one pub to another everything works well. But if I export the soruce pub folders to the target so that I can retain the PF permissions and then turn on the interorg replication I get the following error and new/changed content from the source does not get replicated to the target. What am I doing wrong? Error: unable to create cpublicfoldercontentscollector object. Error: contents update from 'server5\\' to 'server4\\server5 test tree' failed to complete sucessfully. Please provide a clearer step-by-step description of what you did. A...

GL20000
Greetings - I have a client that absolutely insists that She has ability from FRx Viewer to see not only document numbers but CustomerID's or VendorID's. In other words - telephone expenses are $10,000 - Viewer should show - VendorID - Document Number and Amount. Std FRx does not include Vendor or Customer Data. I was checking the GL20000 table. I noticed that the Reference Column and DSCRIPTN column could be updated with VendorID or CustomerID..(from the ORMSTRID column of same table) Question is......... Will updating this table effect anything else in GP? Y...

Counting Two Characteristics from Two different Columns
I have a spreadsheet that lists Account Rep names in Column "A", Client Names in Column "B" and client status in Column "C" (i.e. active or inactive) Bob | Medco | Active Tim | Johnson | Inactive Tim | P-Products| Active Jill | Kaystar | Active Tim | Gemni | Active Bob | Transcorp | Inactive At the top of the Spreadsheet I want to tally the information to indicate that Tim has three clients, 2 Active Client and 1 Inactive client I believe I have done this before, but cannot for the life of me remember what I did - I have tried to do a SUMPRODUCT...

Outlook Runtime Error
I just upgraded my operating system from Windows 98 to Windows XP. I ran the Office disk to update/correct its programs for XP. All of them ( Word, Xcel, etc.) work except for Outlook. Outlook loads then displays a Runtime Error! " This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information." After clicking the OK on the message Outlook closes. What to do? ...

Electronic reconcile error
I have a spec file fromt eh bank that does not have any transaction codes however when i try to say the configuration its asking for a transaction code to be entered in the "Enter field information" if i enter it then the transaction download gives the record does not match error. If the bank file will only contain one transaction type -- I am assuming all cleared checks only -- then map the accout number or any other field that is standard across all records submitted as your transaction code. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC h...

Selective data transfer to other columns
Any help is surely appreciated. I have a sheet where col.a represents expenses, col. b represents numericaly coded reasons for the expense ie: 1,2,3 - owner request, error, unforseen conditions etc.with a legend shown elsewhere. I would like to enter the reason code in b1 and have the value of a1 transfered to the appropriate reason column on row 1. Thanks for any assistance, Tom I think I know what you want! Create a table (maybe your legend) with your codes in the left column, and the reason in the right column. Name the table range "legend". In your A1 cell, in your e...

Excel missing columns when printing to pdf
I have a series of large Excel spreadsheets that need to be printed t pdf. In the print preview, the last few columns are missing, even whe they are included in the print area. Any help would be gratefull received as these large data sheets are a mainstay of our business.. -- DRAWIS ----------------------------------------------------------------------- DRAWIST's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2539 View this thread: http://www.excelforum.com/showthread.php?threadid=38869 Try View - Page break preview and adjust the pages using the blue lines. ...

error message at installation
Trying to install money from my MS Works 99 disc. I get the following error. The keyboard driver specified by the KEYBOARD=line in the [386Enh] section of the SYSTEM.INI file is incompatable with the current version of windows. Running Window 98SE Help! So have you tried installing with that line omitted or commented out? Perhaps the message is correct?!? "slowthorp" <anonymous@discussions.microsoft.com> wrote in message news:011d01c3bdc5$15fc0230$a501280a@phx.gbl... > Trying to install money from my MS Works 99 disc. I get > the following error. > > The key...

avoiding a #value! return
Hi I have a formula ='[Labour Variance Report wk01.xls]Summary'!$N$55+'[Labour Variance Report wk01.xls]Summary'!$N$56='[Labour Variance Report wk01.xls]Summary'!$N$75+'[Labour Variance Report wk01.xls]Summary'!$N $81+'[Labour Variance Report wk01.xls]Summary'!$N$82 some of the referenced cells have a #value! error so I get an error in the returned cell. i want to replace any #value! errors in the above formula with a 0. How do I do this please? Thanks Brian =IF(ISERROR(formula),0,formula) -- --- HTH Bob (there's no email, no snail mail, bu...

changing values in a Word Table
I need to read through a word document (Table) The table has merged cells for Heading Part No Description Price ==================== Widgets A12344 Widget 1 � 5.99 WA123 Widget 2 � 6.45 Thinggies D555 Thinggy 1 � 12.67 RT6161 Thinngy 2 � 17.50 I need to produce a Trade Price List so say I deduct 20% from each value Can't seem to get the VALUE into a a NUMBER format !!! Sub Trade() ' ' Calculate Trade Price List ' Application.ScreenUpdating = False Dim x As Integer Dim LastRow As Long 'get used LASTROW La...

Avoid drawing blank values
Hello, Can you tell me if there is a way to plot only non-blanked values from a table: F.e. if: X= 1,2,3,4 Y=10, ,10,10 (so no value for x=2) I want to obtain a continuous line going from the first point (1,10) to the third point (3,10) and then of course to (4,10). Thanks in advance for your help, Iv�n ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ On Mon, 10 Nov 2003 10:52:34 -0500, ivarela1 = <ivarela1.woqln@excelforum-nospam.com> wrote: > Can you t...

Finding the first value equal to or less than 0
Basically i have hundreds of rows the top row has a date going out for many columns , so picture this 1/1 2/1 3/1 4/1 5/1 6/1 10 8 7 0 -4 -6 -8 and then rows of numbers, with the date only in the first row I need to find out the first number equal to or less than 0 in each row and pull the date for that number. So, when searches down this row in the example above, it sees 0, which is in the fourth column, that fourth column refers to the date 4/1. Picture it as many bank accounts that are depleting. At what date will the bank account become empty or negative one thing to note, somet...

Default value from previous record
I have a data entry form thas has 4 fields (id, date, location, person) and 10 option groups corresponding to questions from a survey. Many surveys are entered at once, so the information for date, location, person are the same for each data entry session. I am trying to carry over the date, location, person fields to the next record. I have tried some VBA which allows assigning all the default values from last record. The event procedure code is supposed to exclude the option groups from carrying over, but they all do. Is there an easier way to solve this, such as just carrying over the 4 fie...

listing values to choose from based on value in another cell
What I am trying to do is list a drop down values based on the value selected in another cell. Possibly you could adapt this technique to your situation: http://www.contextures.com/xlDataVal02.html Debra Dalgleish's site. -- Regards, Tom Ogilvy "MT" <MT@discussions.microsoft.com> wrote in message news:911AADA8-201C-4586-8FAC-F9D1B9AF2062@microsoft.com... > What I am trying to do is list a drop down values based on the value selected > in another cell. ...

how do i count the freqency of values between certain ranges?
i have a set of 10 bin ranges i need to calculate the frequnecy that a set of numbers occur, i tried using the freqency function but cant get it to work correctly Here is an example using equally spaced bins. Say we have values from 0 thru 99 in column A in cells A1 thru A1000 and the bins are: 0-9 1 10-19 2 20-29 3 30-39 4 40-49 5 50-59 6 60-69 7 70-79 8 80-89 9 90-99 10 In B1 enter: =ROUNDDOWN(A1/10,0)+1 and copy down each value in A has the bin number next to it. In C1 enter: =COUNTIF(B1:B1000,ROW()) and copy down thru C10 C1 thru C10 tell you how many values are in each bin. -- ...

Creating a Formula to Format Column automatically? #3
Can I record it as a macro or anything like that? I have to run it o five sheets. I've tried to conditional formatting, and it works only o one cell, unless I'm doing it wrong -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 not sure what you are trying to do but conditional formating can be coped and pasted. you have also conditional format an entire column/row by selected the en...

Automatic column deletion
In a nutshell, is there any way to have a column delete itself automatically based on established parameters...specifics follow. I am running Excel 2002 SP2 I receive an excel spreadsheet feed from an external source. The feed consists of everything shipped out of a distribution warehouse to all locations it ships to in the world. The material is supposed to be segregated by destination. That is; the contents of each container is supposed to be destined for only one location. The contents and its destination are burned onto a Radio Frequency (RF) tag, which is used for tracking the shipm...

SMTP Error # 0x800ccc60
I've been seaching for days for a solution to this one. I have two users on seperate XP Sp2 computers that can no longer send mail. We have been changing workstation logins and these two were fine until I set up their e-mail in Outlook under the new logon. Then the above error started showing up on one box and 0x800cccoe on the other. The sever settings are correct, and I can set up their account on a different box and it will work fine. I found a discussion about McAfee blocking SMTP traffic in some situations, so I disabled McAfee on one of the computers, to no avail. I went to the exte...

'Runtime Error 2448'
I keep getting 'Runtime Error 2448' when I run the code below. When I debug it highlights me.Filter=strWhere. Any suggestions?? Private Sub Filter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.TypeFilter) Then strWhere = strWhere & "([Type]=""" & Me.TypeFilter & """)And" End If If Not IsNull(Me.StatusFilter) Then strWhere = strWhere & "([Status]=""" & Me.StatusFilter & """)And" End If lngLen = Len(strWhere) - 5 If lngLen <= 0 Then Ms...