Replace zeros with "-" in Reports

Hello Experts,

I have a report that based on a query.  The query adds up my monthly hours 
for the people that work on the project and the report displays the result.  
my formula in the query to calculate total forJanuary for example is 07Jan: 
Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]). So all I do is add 
the field 07Jan into my report. The problem am having is I like to replace 
all the 0.00 totals with "-". I try to do IIF(Nz([07Jan]=0,"-",Nz([07Jan]) 
but I get an #Error in the repot.  The only way I got to work is by using a 
text box and if I repeat the whole formula in the report as well by saying:
IIF(Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4])=0,"-",Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4])) 
but I am defeating the point of using the query to calcuate my totals. I am 
sure there is a simple way to do it but can't get there

Help is always appreciated 

Hadi
0
Utf
7/20/2007 5:50:04 PM
access 16762 articles. 3 followers. Follow

1 Replies
1692 Views

Similar Articles

[PageSpeed] 34

Hadi,

Yes, there is an easier way.

Use the format property which does not change the value of a numeric field - 
only the way it looks. There are four sections separated by a semi-colon, 
Positive;Negative,Zero;Null. Suppose  you want negative numbers to be 
enclosed in brackets and dash instead of 0 - set the Format property of the 
field on the report to:

0.00;(0.00);"-"

Regards/JK


"Hadi" <Hadi@discussions.microsoft.com> wrote in message 
news:F3FB038F-A788-4F63-9601-BD26D5A92A9C@microsoft.com...
| Hello Experts,
|
| I have a report that based on a query.  The query adds up my monthly hours
| for the people that work on the project and the report displays the 
result.
| my formula in the query to calculate total forJanuary for example is 
07Jan:
| Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]). So all I do is 
add
| the field 07Jan into my report. The problem am having is I like to replace
| all the 0.00 totals with "-". I try to do IIF(Nz([07Jan]=0,"-",Nz([07Jan])
| but I get an #Error in the repot.  The only way I got to work is by using 
a
| text box and if I repeat the whole formula in the report as well by 
saying:
| 
IIF(Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4])=0,"-",Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]))
| but I am defeating the point of using the query to calcuate my totals. I 
am
| sure there is a simple way to do it but can't get there
|
| Help is always appreciated
|
| Hadi 


0
JK
7/20/2007 11:59:17 PM
Reply:

Similar Artilces:

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Replace zeros with "-" in Reports
Hello Experts, I have a report that based on a query. The query adds up my monthly hours for the people that work on the project and the report displays the result. my formula in the query to calculate total forJanuary for example is 07Jan: Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]). So all I do is add the field 07Jan into my report. The problem am having is I like to replace all the 0.00 totals with "-". I try to do IIF(Nz([07Jan]=0,"-",Nz([07Jan]) but I get an #Error in the repot. The only way I got to work is by using a text box and if I repeat th...

Crystal Reports 06-14-04
What is the quickest and best way to learn Crystal reporting ? I want to be able to create new reports, modify existing ones. I will appreciate if someone can give some pointers like good books, e-course, websites, any good resources. Thanks. ...

Crystal Reports 01-11-05
I have been using a custume report with CRM for several months. Now when I try to open the report using Crystal reports 9, I get the message "The database DLL 'crdb_mscrm.dll" could not be loaded." Any ideas what I need to do? are your standard crm reports working? you may just want to reopen the report in crystal designer and republish it to crm -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "brellim" <brellim@discussions.microsoft.com> wrote in message news:602F987A-FAC6-4E4B-84A9-B54...

JunkE-mail Report Tool does not report spam marked Do Not Forward
Most of the spam I get is apparently marked "Do Not Forward." As such, the Microsoft Junk E-mail Reporting Tool 1.0 will not report it as junk. It can be forwarded though. With no settings or help or feedback, and no way to override an obvious spam trick, this Junk E-mail Reporting Tool is useless. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion ...

RANK
I am trying to figure out how to use the rank function on a column of data. The trick is that i dont want it to include or rank zero's. To achieve a ranking the number should be a positive number. Here is what i have so far. I dont care if it duplicates a ranking as long as the numbers are EXACTLY the same. like two items can be ranked 5 if they are both $6.06 =RANK(I5,$I5:$I40,1)&" of "&COUNTIF($I$5:$I$40, ">0") any help would be greatly appreciated. What do you have in the cells I5:I40 ? If there is a formula then perhaps you can change it to: =3DIF...

Creating a report 12-10-07
Why can't i create a report ? When I press 'Create report in design view' nothing happens. When I press the wizard I can do the wizard as usual, but the ends with 'The wizard is unable to create your report'. Thx in advance I expect you don't have a properly defined default printer in the Windows Control Panel. Try change your default or update your printer drivers. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "Walten" wrote: > Why can't i create ...

Report
Hello, I'm writing a report that retrives the value of a custom field attribute. The problem I'm having is that I cannot find another table/view that contains the label names for each of these value. Thanks in advance, Loreeta The labels are in FilteredStringMap view. -- This posting is provided "AS IS" with no warranties, and confers no rights. "Loreeta" wrote: > Hello, > > I'm writing a report that retrives the value of a custom field attribute. > The problem I'm having is that I cannot find another table/view that contains >...

reports
how do i include my cash positions in my investment reports thank you ...

Find and Replace 05-27-10
We are using a scanner to input a barcode number into "Find What" box in find and replace in excel. Is there a way to make it stay highlighted for the next input without having to use the mouse to double click to re-select after each input? ...

Querying data
I have to create a report for the query below SELECT ClericalMetrics.PS_ID, PerformanceSupervisor.PerformanceSupervisor, ClericalMetrics.[Time Sheet Errors], Count(ClericalMetrics.[Time Sheet Errors]) AS [CountOfTime Sheet Errors], ClericalMetrics.TimeSheetErrors2, Count(ClericalMetrics.TimeSheetErrors2) AS CountOfTimeSheetErrors2, ClericalMetrics.TimeSheetErrors3, Count(ClericalMetrics.TimeSheetErrors3) AS CountOfTimeSheetErrors3, ClericalMetrics.TimeSheetErrors4, Count(ClericalMetrics.TimeSheetErrors4) AS CountOfTimeSheetErrors4 FROM ClericalMetrics LEFT JOIN PerformanceSuper...

Report Writer
I would like to confirm that there is no way to include details about Configured BOMs (generated through the Sales Configurator) on a SOP document. I expect this is so due to Report Writer's inability to access tables which are not listed in the Core Dynamics dictionary. ...

Leading Zero 12-07-09
I have a cell where the number has a leading zero ( Ex - 09123456789 ). But when i select the cell , on the formula bar the leading zero is not showing ( it shows 9123456789 ) how do i change this format. I want to see the leading zero in the formula bar !!! What you see within the formula bar of the cell is always the real, true value. If you want the leading zero to be included as part of the real value, you must either format that cell as TEXT first, then key-in the leading zero (or precede the leading zero input with an apostrophe). Any worth? hit the YES below -- Max S...

Converting Crystal report from MSCRM 1.2
I have a client who has upgraded to V3.0 for CRM. They want to continue using Crystal for their reporting engine because they have 40 custom reports. Does anyone know how to easily accomplish this? I have created a new connection to the new CRM Views in SQL, but I can't seem to figure out how to easily replace all the fields that are currently in the report using the SSO connection to my new connection. Any help would be greatly appreciated. Thanks, Amy many of the services such as rpttosql.com say they will only convert so far. some even say they will only get the general lay...

Sales Percentage
I am novice trying to setup a report, shown below is the requirements. Any help will be appreciated. //--- Report Summary --- // //--- In prequery1, I wanted to take total of quantity(WAREHOUSE QTY) for each ITEMLOOKUPCODE and store it somewhere for calculation and print in the report ---// //-- In the TablesQueried, I wanted to find total of sales(SALE QTY) from store for the same ITEMLOOKUPCODE, then calcuate & print the %age of sale Sort the % age in ascending order in the final report--// PreQuery1 = "SELECT ITEMLOOKUPCODE, (SUM(QUANTITY)) FROM VIEWITEMMOVE...

Reporting 07-04-07
Hi 1.Can the system support consolidated reporting across location and LOB? 2. Is All fields are available for ad-hoc reporting? 3. Can report of any field in the system? Hi VP, Microsoft CRM doesn't actually provide any reporting capabilities. The reporting capabilities are all provided by SQL Server Reporting Services. Reporting Services reports can be published and made available to users through the CRM user interface. SQL Server Reporting Services allows consolidated reporting across locations and lines of business. Nearly all fields are available for reporting, even custom...

Intercept word's built-in command "Search and Replace (with its tabs "search, replace, GoTo)"
Dear Experts: I would like to intercept Word's built in command for Search and Replace in a Macro. How? Help is much appreciated. Thank you very much in advance. Regards, Andreas Just create macros called EditFind, EditReplace, and/or EditGoto and they will be run instead of the respective commands. -- Enjoy, Tony www.WordArticles.com "andreas" <andreas.hermle@gmx.de> wrote in message news:a5d54188-d1ee-4829-9b07-18cb4875b022@q15g2000yqj.googlegroups.com... > Dear Experts: > > I would like to intercept Word's built in command ...

Replace "-" with tab
Hi I have a question regarding replacement of a character with tabulator. I have to replace "-" with tabulator for one column. The column has several words separated with "-". Looks like this: SFS-HV-AFS I would like to have it seperated into several columns for each word. If I could replace "-" with a press of tab, that would do it... But hooow? :) Thank you for your time... On Mon, 17 Dec 2007 02:17:46 -0800 (PST), ZUZ3L <kennethbrogger@gmail.com> wrote: >Hi > >I have a question regarding replacement of a character with tabulator. > >...

crystal reports 03-05-04
hi everybody, in crm 1.2 crystal reports is giving check for if you have enough licences. all services in crystal manager is running & all services are enabled. can somebody help me! Did you by chance attempt to load Crystal Reports seperate from the CRM? I did that and it over wrote my current run times and i got that error. My fix was to reload the CRM, make sure you have a good backup of it before hand but i did not have to restore it. Hope this helps Dan >-----Original Message----- >hi everybody, > >in crm 1.2 crystal reports is giving check for if you have > ...

Delete a row if it contains only zeros
Hey, Is it possible to delete an entire row if it contains only zeros s that the row underneath moves up. I would like this to be able t happen automatically or at the click of one button. Is this possible? Thanks -turner200 -- turner200 ----------------------------------------------------------------------- turner2000's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1477 View this thread: http://www.excelforum.com/showthread.php?threadid=26444 Here is a script I've used for this purpose. Sub RemoveZeroRows() Application.ScreenUpdating = False Dim X ...

How to launch report from add-in?
Hi all, I am trying to launch a report from POS addin(RMS). but it is giving an error related to the storng name of the assembly. Can anyone please tell me how I can use the native crystal report engine that is there with POS. so that I need not embed my own crystal report dll. Any other suggestion would help. thanks Hi, The company that made some addins for me and use some printing from the addin, added the software: Crystal Reports for .NET Framework 2.0 (x86) After installing CRRedist2005_x86.msi into my system I can use their reports. Koit Lahesoo "KSS" <KSS@dis...

Ignore Zero's
Hi I have used the following to get the average value from a set of data whilst ignoring zero values within the data set. =SUM(K4:AP4)/COUNTIF(K4:AP4,">0") I would like to use the percentile command on the same set of data. Is there a way that I can use Percentile and also ignore zero values ? Many Thanks Chris Hi, with an ARRAY formula =PERCENTILE(IF(K4:AP4>0,K4:AP4),0.1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formu...

Report Help
Hi All, I am running a report linked to a query in which the criteria is pick up from a form using dates (using the "between" statement) In my report PAGE HEADER i have the following code in two text boxes so the I can show the "To" and "From" dates =[Forms]![weekly summary date select].[startdate] =[Forms]![weekly summary date select].[enddate] When I run the report it has several pages but the two dates only appear on the first page, on the consecutive pages the #Name? is dispalyed in the two text boxes, how do I get these additional pages to d...

Need help with "Year-to-Date" Report -MONEY 2000 #2
Hello! Using MS MONEY 2000, I finished entering all my data for January 2006 , and I ARCHIVED JAN 06,. I then started on February 06. But my check register still shows all of January's activity; is that the way it supposed to be? Couldn't I start a fresh Check Register for February 06? Also, I would like a 'Report' to show: a January column, a February column, and finally a YEAR TO DATE column.....showing the accumulated totals for the two months, a month later, three months and eventually 12 months, etc. Is that possible? I sure would be happy if I could have it tha...

Stanley 47-101 30m/100 Replacement Chalk Line
Price:$1.05 Image: http://megadiscountguru.info/image.php?id=B000BO92HM Best deal: http://megadiscountguru.info/index.php?id=B000BO92HM 100' Replacement Chalk Line, High Quality Replacement Line, 100', 30M On Reel. ACCESSORIES: This Old House (1-year):http://megadiscountguru.info/index.php?id=B00005R8BL Measuring, Marking, and Layout: A Builder's Guide:http://megadiscountguru.info/index.php?id=1561583359 ...