how to get a default value of 0 instead of null in a crosstab

[urbanista]

I'm working with a database of two tables, persons and
trips. I want to create a crosstab showing number of trips
by type for each person. However, I get null values
whenever a person has not completed any trips of a
specific type, and this confuses my statistical program,
which needs zeroes. Is there any way to set the
default "count" in the value field to zero instead of null?

 

[Allen Browne]

Switch the query to SQL view (View menu), and add Nz() to the expression in
the TRANSFORM clause, e.g.:
TRANSFORM Nz(Sum([MyField]),0) AS SumOfMyField ...

 

[urbanista]

what about when I'm using count? I tried count([Myfield])
+0 and that didn't seem to work.

-----Original Message-----
Switch the query to SQL view (View menu), and add Nz() to the expression in
the TRANSFORM clause, e.g.:
TRANSFORM Nz(Sum([MyField]),0) AS SumOfMyField ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm working with a database of two tables, persons and
trips. I want to create a crosstab showing number of trips
by type for each person. However, I get null values
whenever a person has not completed any trips of a
specific type, and this confuses my statistical program,
which needs zeroes. Is there any way to set the
default "count" in the value field to zero instead of null?

.

 

[hermie]

Youasked this before?

Format the field with 0;;;0 (page 760 of the access bible book)

Hermie

 

[Allen Browne]

Try Nz() around your Count().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

what about when I'm using count? I tried count([Myfield])
+0 and that didn't seem to work.

-----Original Message-----
Switch the query to SQL view (View menu), and add Nz() to the expression in
the TRANSFORM clause, e.g.:
TRANSFORM Nz(Sum([MyField]),0) AS SumOfMyField ...

I'm working with a database of two tables, persons and
trips. I want to create a crosstab showing number of trips
by type for each person. However, I get null values
whenever a person has not completed any trips of a
specific type, and this confuses my statistical program,
which needs zeroes. Is there any way to set the
default "count" in the value field to zero instead of

null?

 

[Allen Browne]

You are aware that there is a difference between replacing a null with a
zero, and displaying a null as a zero?

Example:
The average of 4, 8, and Null is 6, i.e. 12 divided by 2.
The average of 4, 8, and 0 is 4, i.e. 12 divided by 3.

 

[urbanista]

Thanks - I'm not sure why, but Nz() by itself seemed to
cause my program to lose track of the file it was writing.
I used Val(Nz(Count(FIELD),0)) in the end.

Also it's true that 0;;;0 didn't help with my export
problem.

-----Original Message-----
Try Nz() around your Count().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

what about when I'm using count? I tried count ([Myfield])
+0 and that didn't seem to work.

-----Original Message-----
Switch the query to SQL view (View menu), and add Nz()

to
the expression in

the TRANSFORM clause, e.g.:
TRANSFORM Nz(Sum([MyField]),0) AS SumOfMyField ...


I'm working with a database of two tables, persons and
trips. I want to create a crosstab showing number of trips
by type for each person. However, I get null values
whenever a person has not completed any trips of a
specific type, and this confuses my statistical program,
which needs zeroes. Is there any way to set the
default "count" in the value field to zero instead of

null?

.

 

[Allen Browne]

That's interesting!

JET is actually very poor at recognising the data type returned by an
expression, so it needed Val() as well? Yes, we often do see that issue, as
discussed in this article:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html