#### NZ Function and Null VS 0 values

Here is my question, I have the following statment in a query:

PostSumCreated: Sum(NZ([Post_Sec2_7CreatedTotal])
+NZ([Post_Sec3_5CreatedTotal])+NZ([Post_Sec4_3CreatedTotal])
+NZ([Post_Sec4_5CreatedTotal])+NZ([Post_Sec5_4CreatedTotal])
+NZ([Post_Sec6_7CreatedTotal])+NZ([Post_Sec7_4CreatedTotal]))

This returns either the number of the addition or 0. The problem is
that I have NULL values in the table, where 0 would represent that yes
nothing was done and Null would represent that nothing was support to
happen.  What I need help with is trying to add the needed columns if
they have a value >= 0 or leave a blank in the table if there was
nothing entered in any of the fields. Any one have any ideas?

~Amanda~

 0
AmandaH
6/8/2007 5:04:17 PM
access 16762 articles. 3 followers.

1 Replies
1368 Views

Similar Articles

[PageSpeed] 35

Thanks for trying to help, I figure out my problem. If at least one of
those fields has a 0 in it instead of a null value then it will return
the zero.

Thanks.

 0
AmandaH
6/13/2007 7:15:12 PM

Similar Artilces:

IF function help
I have a worksheet that someone wants to have all the blank cells in the range display a "0" and the other cells to display whatever is in them. I have tried to figure out an IF function, but don't know what to put for the false part. Is there a way to have it just display what is in the cell as the false?? Try =IF(A1="",0,A1) It should work for the situation described below. >-----Original Message----- >I have a worksheet that someone wants to have all the >blank cells in the range display a "0" and the other cells >to display whatev...

specify a values for (x) axis in bar chart
how can i specify a values for (x) axis in bar chart in Excel ?? My (y) axis is percent and i need to change the width of bar on (x) axis, but it seems that i can just change the category of it. my project is that for example from 10 to 90m we have 20%, 30% and 50% of three material, from 90 to 200m we have 10% and 90% of two material, etc. I also welcome other suggessions to show this on table. The only 2 charts in the XL domain that allow values for the x-axis are XY Scatter charts and Bubble charts. For other charts, if and only if the x-values are dates, one can set the x-axis...

RMS 2.0 startup error
Hi, I get a small box opening up with the word "setwaitchar" in it and "Incorrect function" as well. Eventually if I persist by clicking it a million times SOM or POS opens. Any clues this pain -- Regards Dan Levin ...

How do you close a CRM 3.0 Service Ticket?
We just started using CRM 3.0 and are testing it out. There doesn't seem to be a way to close a service ticket in CRM 3.0. How do you close a ticket in CRM 3.0? While inside the case record, from the actions menu, select resolve case or cancel case. "DeWayne" wrote: > We just started using CRM 3.0 and are testing it out. There doesn't seem to > be a way to close a service ticket in CRM 3.0. How do you close a ticket in > CRM 3.0? I figured it out just moments before reading your post, but thanks for the help anyway. "Jason Lattimer" wrote: > ...

Option Group Won't Hold Value
I have an option group with four option buttons for "Titles," (Mr., Mrs., Ms., Dr.,)... I have code in the after update event of the option group to populate a combo box with the selection from the option group. If I click Mr. in the option group, Mr. appears in the combo box BUT, the option button next to Mr.: does not remain selected. All of the option buttons are un-selected whether I click one or not. I cannot for the life of me figure out why this is happening!!! Pls Hlp!!! Is this happening after the event or when you reopen the form the next time? Most likely the opti...

Using Access form to assign values of variables in an Excel VBA program
I have created a VBA program for Ms Excel.But,to get desired results, every time, I go into the code to change the values of variables.Is there a way to get the values of variables stored in an Access table? YOu need to do a search for ADO method (ActiveX Data Object). There are plenty of example of code on the web. You need to add two references in your VBA to use the method. from VBA menu tools - References 1) Microsoft Access XX.X object library 2) Microsfot ActiveX Data Objects X.X library Use the latest versio of the two libraries on your PC. You will make a conn...

Upgrading reports from CRM 3.0 to 4.0
Having spent around 10 hours trying to upgrade a custom report from CRM 3.0 to CRM 4.0, I thought I’d post a message to share my pain and the solution! It appears that in CRM 4.0 Microsoft have made a change to the way in which the standard reports retrieve the name of the user running the report. I have been unable to find any documentation about this. If you have a custom report that is based on a standard CRM 3.0 report (i.e. it has the CRM_FullName parameter & UserInfo dataset) and upload it into CRM 4.0 then try to run it, you may get either of the following behaviours • Th...

Closed Opportunity Description in Database
When you close an opportunity you can enter a description, this does not get added to the description for the opportunity, and I can't figure out where to get it either to use in an email template or what database table it is in. Anyone know? This is in CRM version 4.0. There is a systm entity called Opportunityclose. It is available in the database FilteredOpportunityClose view. I don't think you can get to it from an email template, but you definately can from a report/SQL query. This is the same story for the case resolution description. "JR" wrote: > Whe...

Filemaker vs. Access
Is Filemaker better than Access? I have been using Access since Access97 and find it quite easy to understand and can pretty much do anything with it. Has anyone used both? Comments please. JB All depends on what you are doing with it. My personal opinion: FileMaker is easier for some things. Access does has some powerful features that are missing in FileMaker. At the same time, a lot of users don't need the extra features. FileMaker can be frustrating at times, just because of terminology difference, if you are going from Access to FileMaker. But once you are used to one o...

Hi, Got so many articles but all are perfect. Using CRM 4.0. I would like to include hyperlink in Workflow email. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.crm&tid=bbf7c5cf-4a61-45b4-a8e7-8c37eebcffed&cat=en_US_f9cb8267-48ed-4f92-9f39-209bbcb29eb1&lang=en&cr=US&sloc=&p=1 Issue is: Email generated through workflow, but the http... shows as plain text, not as hyperlink. Any suggestion will be appreciated. -mac Hello John, Did you copy and paste in a hyperlink, or type in the url? You may want to copy and paste a hyper...

Is there something like Word's "CREATEDATE" function in Excel?
I've created a lovely invoice in Excel for my company. When I generate a new invoice from the template it puts in the date using the TODAY() function, but unfortunately this updates if I ever open the invoice again to look at it. In Word there is a "CREATEDATE" function which inserts the date that the file was created first, and never after does it change. Is there any way to accomplish this same task in Excel? Thanks, Michael mleenheer @ yahoo.com ML, Keyboard shortcut to date stamp is put your cursor in the cell you want and push -- ctrl ; --(thats ctrl and semicolon...

CRM 3.0 crm Microsoft CRM-Exchange E-mail Router
HI all, i send the email from CRM to other people. but no have any recipient received the mail. i go event viewer check, no have any error msg found. my CRM 3.0 Server is install in computer a, Microsoft CRM-Exchange E-mail Router is on another server. what can i do to find out the what cause the email can't send. any help will be greatly apprieated. Hi, When i run Rule Deployment Wizard - and apply the rule to accounts, then i run Rule Deployment Wizard -and verify the rules. all users get the error message "The rule is apply but is not enabled" what does this mean? a...

User-defined Functions #2
How can I access the user-defined functions?? I want to modify one of the functions and don't see how to get into it. Thanks in advance... -Andrea Report Writer functions.... shown as user defined functions in calculated fields are created by a Dexterity developer and cannot be created or altered by a user. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessaril...

Selecting top five values from Pivot
Hi, I have a Pivot shows the supplier name and the qty supplied by them. Now in another sheet, I have to show only the top 5 suppliers, who have supplied highest qty. Supplier Qty AAAA 12 BBBB 2 CCCC 65 DDDD 987 EEEE 75 FFFF 1 GGGG 123 Above data is displayed in Pivot. In another sheet, I require, only top 5 suppliers with highest qty supplied. Please help. Thanks, Navin ...

How to extend functionality
Hi, this is my need: I should add an entry to the context menu in the list of email, a heading such as "Send to" to which I could associate my code (such as VB 6.0 COM component) to perform some "custom" operation on email selected. Recognizing that this question is my very first of its kind, ie although I'm developer for many years I never had to address the need to "expand" the functionality of a package of office. I would be grateful to anyone who was in to give me a hand, at least to address me on how/where retrieve documention for this need...

Cell Number Format includeing other Cell Value
Hello, I needed to create some conditional number format - to say so... My goal: Cell A1 content is "m2" as I Enter in cell A2: "10" -> the output to cell A2 should be "10 m2". Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"... and so on.... Is that possible in Excel? (don't know VBA - yet :DD) Any help is appreciated! BR, Daniel How about just use a third cell: =a2 & " " & a1 daniel_of_vienna wrote: > > Hello, > > I needed to create some conditional number format - to say so... > M...

HELP!!! How do you create a query crieria for multi values??
Hello all, please hyelp, its an emergency!!! Need to create a query where one of the searches is male or female, how do I allow user to enter both as the search options?? Also how do I convert tix boxes into a query criteria (e.g. serach for all ticked) Thanks so much for your help ...

Disable/Enable all controls on a form based on value of another control
What is the best to way to exclude a specific control (or controls) from the following condition? (e.g., I would like to exclude SubjectID and MRN from being Enabled or Disabled): ### Dim Ctl As Control On Error Resume Next If IsNull(Me.SubjectID) OR IsNull(Me.MRN) Then For Each Ctl In Me.Controls Ctl.Enabled = False Next Ctl Else For Each Ctl In Me.Controls Ctl.Enabled = True Next Ctl End If ### I'm using this to ensure that the user enters the SubjectID and MRN *before* entering more data for the subject. By disabling all the...

PGO in VS 2005 beta 2
hello I'm trying to use the new Profile Guided Optiomization in Visual Stduio 2005, compiling the default wizard generated MFC application. when choosing in the settings: Whole Program Optimization = Profile Guided Optimization - Instrument I get a link time error saying: c:\projects\ms2005test\ms2005test\ms2005test\ms2005testView.cpp : fatal error C1350: error loading dll 'pgodb80.dll': dll not found LINK : fatal error LNK1257: code generation failed does any one know how to fix this? thanks in advance Shy >I'm trying to use the new Profile Guided Optiomization in V...

New functionalities in version 3.0
Hi, I'm looking for any specyfication of new functionalities in version 3.0. We are writing new functionalites in base of 1.2 version - maybe it will not needed and we spend time. search google for microsoft crm and you will see lots of news items related to version 3.0 -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rafal" <Rafal@discussions.microsoft.com> wrote in message news:9C72D354-3ACC-479D-9B17-BC65036F978A@microsoft.com... > Hi, I'm looking for any specyfication of new functionalities in version 3.0. > We are writing new functionali...

RMS vs. Commerce Manager or other e-commerce
is anyone using rms+commerce manager? what is involved with integrating them? updating inventory db, etc. anyone exporting RMS database for use in e-comm carts like osComm or zen-com? thanks for your time, juglesh B>{)} ...

GP 8.0 and SQL 2000 Optimal File Size
I have questions about SQL Server 2000 databases with Great Plains 8.0 and file size. After the upgrade to 8.0 our Company database has ballooned to a whopping 9 GB (including the transaction log). It was 7 GB before the upgrade. It seems really large to me and was wondering what the recommended settings were for the SQL databases with Great Plains. I have looked in the Knowledgebase and haven't found anything to give me good answers. Here are the specs on the Company database: Database Size = 7,184 MB, Space Used = 4,655 MB Log Size = 2,162 MB, Space Used = 76.99 MB Auto G...

Close Past Appointments automatically in CRM 3.0
In CRM 1.2 there use to be a stored procedure that would automatically close past appointments. In CRM 3.0 the SQL Job is still listed but the stored procedure is gone. Is there a way to get this back in CRM 3.0? Thanks Angela You can do that with workflows. Take a look at this blog post: http://www.cwrmobility.nl/weblog/2006/01/automatically-close-appointments-after.html HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "astuartmills" <astuartmills@discussions.microsoft.com> wrote in message news:46BBBB6B-93AD-451F-810A-C8B42F750221@microsoft.com... &g...

Function VBA
Dear expert, I am not good at function VBA, but I know that can help me to save a lot of memory. Say a cell in K10, L10 and M10 Contain figures 7,8,10 How can I write a function say in J10 type =cross(K10,L10,M10) Outcome can be 4. If And(M10>L10, L10>K10) = Show "All DOWN" If And(K10>L10, L10>M10) = Show "All UP" If And(K10>L10, L10<M10) = Show "K cross L UP" If And(K10<L10, L10>M10) = Show "K cross L down" Now I have a lengthy function in J10 which is going to copy from J10 to J32000 =IF(AND(K10>L10,L10&g...

Check value range, then return spesific number?
Hi. I want to implement a function in my spreadsheet, that gives me this: When I enter a number into a cell, another number shows in another cell, based upon the number first entered. A bit more spesific: The idea is to calculate the number of instructors needed for a week end seminar. 1 participant requires 1 instructor. Same for 2 participants, and 3. 4, 5 and 6 participants requires 2 instructors. 7, 8 and 9 participants requires 3 instructors. And so on. For every 3 participant, we apply 1 instructor. So, i.e. if I enter the number 5, i want the number 2 in the cell below. I trie...