SUM(ISNULL(yourField, 0))

Solved

Posted on 2011-10-04

I have a query that that is supposed to use the NZ function to convert Nulls, if they exist, to Zeroes and give a sum of these values.

I am really trying to count the Nulls in order to determine how many of these fields are blank. I'm not sure if this is the way to do it.

I am really trying to count the Nulls in order to determine how many of these fields are blank. I'm not sure if this is the way to do it.

```
SUM(NZ(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement,0))
```

17 Comments

SELECT Count(*) as NullCount WHERE [Full_Problem_Statement] IS NULL

Fyed...There is much more to the query, so I already have a set of WHERE criteria. I'll have to figure out IF I can make what you have work with them

Try

SELECT Count(Bz_ClosedByWeek_CARs

FROM Bz_ClosedByWeek_CARs_Blank

WHERE (((Bz_ClosedByWeek_CARs_Bl

where SomeOtherField is another field, e.g. PK field, in the table.

OM Gang

I assume that [Full_Problem_Statement] is a memo field in your table? If there is a chance that the field once contained data, but had that data deleted, you will also need to check that the field length is 0 but not NULL.

Try:

Sum(Abs(LEN(Bz_ClosedByWee

That is not possible.

Bz_ClosedByWeek_CARs_Blank

returns True or False

Abs(Bz_ClosedByWeek_CARs_B

returns 1 or 0

SUM(Abs(Bz_ClosedByWeek_CA

Sums the 1s and zeroes.

/gustav

By that I mean that I have another clause, for example, that counts the total fields....

Count(Bz_ClosedByWeek_CARs

...and it returns a zero for the rows where all of the listed columns return a 4, and a 5. I have obviosuly done something worng in trying to count totals as there cannot be more blanks than the total. Note...the above clause does not always return a zero.

would have worked better with the table screen shot...

Count-Null-Returns.jpg

Count-Null-Returns.jpg

Title | # Comments | Views | Activity |
---|---|---|---|

how do you view Microsoft Access Relationships | 2 | 20 | |

querying by the sum of a column in decimal | 7 | 30 | |

get the maximum value on a condition | 3 | 30 | |

Access Query concatenation | 12 | 23 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**22** Experts available now in Live!