By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

change 0 value to "-"

P: 25
hi...

can access change the 0 value in my crosstab into "-" character.
i want that character appear in my report not as "0" but "-'

thanks
Feb 20 '09 #1
Share this Question
Share on Google+
9 Replies


100+
P: 365
in your value field you can put something like this:
Expand|Select|Wrap|Line Numbers
  1. Count: nz(Count([Table.Field]),"-")
  2. 'as an expression
that should work i believe.

Dan
Feb 20 '09 #2

Expert Mod 2.5K+
P: 2,545
If you really mean in a report (and not just for display in the crosstab query itself) you can always apply a custom format using the Format property of the textboxes concerned. The format property can have four sections, separated by semi-colons: format for positive numbers, negative numbers, zero values, and null values.

If you needed two decimal places for positive and negative but '-' for zeros the custom format of the textbox would be

0.00;-0.00;-

The Nz function will only convert null values, not zeros, and anyway has a disadvantage in that it will convert the number displayed to text - which will spoil subsequent attempts to display or format the number in any report based on the query.

Similarly, using, say, the Format function to display custom formatted data in a query also converts the results to text - an annoying limitation which means it is better to apply formatting as the very last step in the chain of processing, preferably within the form or report used to display the data.

-Stewart
Feb 20 '09 #3

P: 25
thank you guys...
it works.
Feb 20 '09 #4

100+
P: 365
@Stewart Ross Inverness
I presumed he would be using his crosstab to count
@Stewart Ross Inverness
Would that be a problem?
Just curious

Dan
Feb 20 '09 #5

Expert Mod 2.5K+
P: 2,545
Hi Dan. Text values that look like numbers cause all sorts of problems. One obvious one is that text values don't sort the same way as numbers
(for example, a text sort of the numbers 1, 10, 11, 20 and 100 is
1, 10, 100, 11, 20). Another, as I mentioned, is that text values cannot be custom formatted in a form or report - so no displaying as currency values if this was required, or specifying decimal places and so on.

The crosstab may well use count - but there again it could have been max, min, last, first, and so on. We don't know (as we weren't told in the question) - so it is safest not to make assumptions about the underlying function when Count is just one of many possibilities.

The poster did mention that the requirement was to substitute for zero values. That is all we had to go on. Ask yourself how Count could ever be the function directly returning a 0 value in a crosstab? Count will either return a value of 1 or greater, or a null if there are no rows to count. It can never itself return a 0.

Any null values returned by the pivot function can be replaced with 0 using Nz - but wrapped in a conversion function to ensure its return value remains a number and not text:

CLng(Nz(Count([somefield]), 0))

Coming back full circle, since this 0 is a number the way to display '-' instead of the 0 is to use the custom formatting as recommended!

-Stewart
Feb 20 '09 #6

NeoPa
Expert Mod 15k+
P: 31,418
Unlike most aggregate functions, Count() actually does return a 0 (rather than Null) when there are no matches. This effects what Stewart said very little however. The points are just as valid. Keep it numeric until you have to, as you have little idea what may want to treat it as numeric in other situations. After all, queries are used as the input to many different objects, including other queries.
Feb 22 '09 #7

Expert Mod 2.5K+
P: 2,545
Hi NeoPa, and thanks for the clarification. In a crosstab query the result of Count(), Sum() etc pivoted on another field will be null if there are no matching rows. The pivot result is like what happens with an outer-join when there are no matching rows to return on the 'optional' side of the join - any fields listed from the optional side return as null for that row.

Here's a test query that represents the minimum possible crosstab - one row header field, a pivot column, and a value. In the example there is a pivot value for gradeID of 'G8' that occurs in just one row, that for ID 'B6'. Note that all other rows show a null value, not a zero, for that entry:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(*) AS Result
  2. SELECT id
  3. FROM gradecounts
  4. GROUP BY id
  5. PIVOT gradeid;
Result
Expand|Select|Wrap|Line Numbers
  1. id    G7    G8
  2. B1     1    
  3. B2     2    
  4. B3     1    
  5. B4     1    
  6. B5     3    
  7. B6     1     1
Anyway, you have captured exactly what I wanted to say about keeping values numeric for as long as possible before doing anything that changes them to some other type.

There are useful tips on crosstabs on Allen Browne's site, at http://allenbrowne.com/ser-67.html.

-Stewart
Feb 22 '09 #8

NeoPa
Expert Mod 15k+
P: 31,418
I do beg your pardon Stewart.

I thought it was unlikely that you'd make such a mistake. I should have known better.

I must admit that cross-tabs are something I never use, and consequently have very little experience of.
Feb 22 '09 #9

100+
P: 365
I made the same mistake and assumed the OP was requesting something i already did in one of mine.

Dan
Feb 22 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.