Null value in query

  • Thread starter Thread starter Rob Drummond, Jr
  • Start date Start date
R

Rob Drummond, Jr

I have created a query that shows [signature on paperwork] and [damage
noted], then gives a count for "damage noted". What I want is for the query
to return zero if there is no value. In other words, if there are 5 names
and only 3 have "noted" values, I want all 5 names listed with 2 having "0"
as the count instead of only the 3 names that have values. I can not seem to
get the expression correct.
 
hi Rob,

Rob said:
I have created a query that shows [signature on paperwork] and [damage
noted], then gives a count for "damage noted". What I want is for the query
to return zero if there is no value. In other words, if there are 5 names
and only 3 have "noted" values, I want all 5 names listed with 2 having "0"
as the count instead of only the 3 names that have values.
Click to expand...
I'm not sure what you mean with count here, but

SELECT
[signature on paperwork],
Nz([damage noted], 0) AS DamageCount
FROM <yourTable>

should do it.


mfG
--> stefan <--
 
Stefan Hoffmann said:
hi Rob,

Rob said:
I have created a query that shows [signature on paperwork] and [damage
noted], then gives a count for "damage noted". What I want is for the query
to return zero if there is no value. In other words, if there are 5 names
and only 3 have "noted" values, I want all 5 names listed with 2 having "0"
as the count instead of only the 3 names that have values.
Click to expand...
I'm not sure what you mean with count here, but

SELECT
[signature on paperwork],
Nz([damage noted], 0) AS DamageCount
FROM <yourTable>

should do it.


mfG
--> stefan <--
Click to expand...

By count, I mean that I display the query as a calculation with [signature
on paperwork] total being "group" and [damage noted] total being "count".

I am beginning to suspect I am using the wrong type of query. I think what
I need is a crosstab query that I can filter by date range. But thank you
for the help and I will try this to see if it works.
 
hi Rob,

Rob said:
I am beginning to suspect I am using the wrong type of query. I think what
I need is a crosstab query that I can filter by date range. But thank you
for the help and I will try this to see if it works.
Click to expand...
Use the query designer. Select your table, then your fields. Press the
sum symbol (Greek S)...


mfG
--> stefan <--
 
Hi Stefan,

I have made a crosstab query that give me what I was data wise (except being
able to filter by date range, but that is another matter). If I want ull
values displayed as zero, would I use the statement you gave me earlier
(SELECT
[hx signature on paperwork],
Nz([damage noted], 0) AS DamageCount
FROM DamagesByTechAllQry)?

I tried adding it to the end of the SQL but I get a Syntax error. Here is
the SQL:

TRANSFORM Count(DamagesByTechAllQry.[Serial Number]) AS [CountOfSerial Number]
SELECT DamagesByTechAllQry.[HX Signature on Paperwork],
Count(DamagesByTechAllQry.[Serial Number]) AS [Total Of Serial Number]
FROM DamagesByTechAllQry
GROUP BY DamagesByTechAllQry.[HX Signature on Paperwork]
PIVOT DamagesByTechAllQry.[Damage Noted?];


Can I use the statement you gave me? If so, how should I insert it? I
think I was trying to get the results the wrong way.
 
hi Rob,

Rob said:
I tried adding it to the end of the SQL but I get a Syntax error. Here is
the SQL:
Can I use the statement you gave me? If so, how should I insert it? I
think I was trying to get the results the wrong way.
Click to expand...
Post your working crosstab query.


mfG
--> stefan <--
 
Here is the working Crosstab:

TRANSFORM Count(DamagesByTechAllQry.[Serial Number]) AS [CountOfSerial Number]
SELECT DamagesByTechAllQry.[HX Signature on Paperwork],
Count(DamagesByTechAllQry.[Serial Number]) AS [Total Of Serial Number]
FROM DamagesByTechAllQry
GROUP BY DamagesByTechAllQry.[HX Signature on Paperwork]
PIVOT DamagesByTechAllQry.[Damage Noted?];


Currently, it shows a blank field for any null values. I would like it to
show a "0" for null values.
 
hi Rob,

Rob said:
TRANSFORM Count(DamagesByTechAllQry.[Serial Number]) AS [CountOfSerial Number]
SELECT DamagesByTechAllQry.[HX Signature on Paperwork],
Count(DamagesByTechAllQry.[Serial Number]) AS [Total Of Serial Number]
FROM DamagesByTechAllQry
GROUP BY DamagesByTechAllQry.[HX Signature on Paperwork]
PIVOT DamagesByTechAllQry.[Damage Noted?];
Click to expand...
Currently, it shows a blank field for any null values. I would like it to
show a "0" for null values.
Click to expand...
You need to enclose the underlying field with Nz(). Imho you need

PIVOT Nz(DamagesByTechAllQry.[Damage Noted?], 0)

at the end.

But the easier way: Create a query selecting the values you need. Use
Nz() for the columns which return NULL. Save this query and built a new
crosstab with it.

mfG
--> stefan <--
 
Back
Top