change 0 value to "-"

[dbdb]

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

[Dan2kx]

in your value field you can put something like this:

Count: nz(Count([Table.Field]),"-")
'as an expression

that should work i believe.

Dan

[Stewart Ross]

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

[dbdb]

thank you guys...
it works.

[Dan2kx]

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

Dan

[Stewart Ross]

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

[NeoPa]

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.

[Stewart Ross]

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:

TRANSFORM Count(*) AS Result
SELECT id
FROM gradecounts
GROUP BY id
PIVOT gradeid;

Result

id    G7    G8
B1     1    
B2     2    
B3     1    
B4     1    
B5     3    
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

[NeoPa]

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.

[Dan2kx]

I made the same mistake and assumed the OP was requesting something i already did in one of mine.

Dan