[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

My Query that Sums Nulls Returns a Data Type Mismatch Error

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.
SUM(NZ(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement,0))

Open in new window

0
Rex85
Asked:
Rex85
  • 6
  • 3
  • 3
  • +5
5 Solutions
 
Roman GhermanCommented:
in MS SQL we use:

SUM(ISNULL(yourField, 0))
0
 
Dale FyeCommented:
if all you want to do is count the NULLs then try:

SELECT Count(*) as NullCount WHERE [Full_Problem_Statement] IS NULL
0
 
Gustav BrockCIOCommented:
Try with:

SUM(Abs(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement Is Null))

/gustav
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ThomasianCommented:
SUM(IIF(ISNULL(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement),1,0))

Open in new window

0
 
Rex85Author Commented:
roma1123: - I got an incorrect number of arguments error
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
0
 
omgangCommented:
What is the data type of Full_Problem_Statement?  I'm thinking it is NOT numeric and that is why you're receiving the error.

Try

SELECT Count(Bz_ClosedByWeek_CARs_BlankCount_Tbl.SomeOtherFiled) AS CountOfSomeOtherField
FROM Bz_ClosedByWeek_CARs_BlankCount_Tbl
WHERE (((Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement) Is Null));


where SomeOtherField is another field, e.g. PK field, in the table.
OM Gang
0
 
Rex85Author Commented:
Cactus Data and Thomasian: I got a Data Type Mismatch Error when trying to execute your clauses.
0
 
Rex85Author Commented:
Looks like Full_Problem_Statement is a Memo Field
0
 
Dale FyeCommented:
Are you running Access against an Access db or do you have some other backend (SQL Server, My SQL, ...)?

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_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement & "")= 0))

0
 
mbizupCommented:

 SUM(Abs(IsNull(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement)))
0
 
Rey Obrero (Capricorn1)Commented:
try this


Sum(IIf([Bz_ClosedByWeek_CARs_BlankCount_Tbl].[Full_Problem_Statement] & ""="",1,0))
0
 
Gustav BrockCIOCommented:
> Cactus Data ..: I got a Data Type Mismatch Error when trying to execute your clauses.

That is not possible.

  Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement Is Null

returns True or False

  Abs(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement Is Null)

returns 1 or 0

  SUM(Abs(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement Is Null))

Sums the 1s and zeroes.

/gustav
0
 
Rex85Author Commented:
Cactus_data: you are correct. Something else must have been generating that error. Actually, as you can see from the query output, most of the suggestions here work, and return identical results. I am just trying to figure out what those results mean.

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

Count(Bz_ClosedByWeek_CARs_BlankCount_Tbl.Full_Problem_Statement) AS Tot_Prob_Stmnt,

...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.
0
 
Rex85Author Commented:
would have worked better with the table screen shot...
Count-Null-Returns.jpg
0
 
Dale FyeCommented:
When you count a particular column, it will not include the NULL values.  So to count the total records, I generally count on the autonumber column, something like:

COUNT(ID) as RecCount
0
 
Rex85Author Commented:
Thank you all very much! I really do appreciate the help.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now