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

  • Thread starter Thread starter urbanista
  • Start date Start date
U

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?
 
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 ...
 
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?
Click to expand...


.
Click to expand...
 
Youasked this before?

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

Hermie
 
Try Nz() around your Count().

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

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

urbanista said:
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
Click to expand...
Click to expand...
null?
Click to expand...
 
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.
 
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()
Click to expand...
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
Click to expand...
null?
Click to expand...


.
Click to expand...
 
Back
Top