Hi there,
I am using Access 2010 and one of my queries doesn't show zero when there are no results, it puts nothing. I need it to show zero and not nothing, please can someone help I am sure its a easy fix.

The only field that is ticked to show is Total.

The sql code is this:-
SELECT [Quarter 2].[Attendance one]+[Attendance two]+[Attendance three]+[Attendance four]+[Attendance five]+[Attendance six]+[Attendance seven]+[Attendance eight]+[Attendance nine]+[Attendance ten]+[Attendance eleven]+[Attendance twelve]+[Attendance thirteen]+[Attendance fourteen] AS Total
FROM [Quarter 2]
WHERE ((([Quarter 2].[Attendance one]+[Attendance two]+[Attendance three]+[Attendance four]+[Attendance five]+[Attendance six]+[Attendance seven]+[Attendance eight]+[Attendance nine]+[Attendance ten]+[Attendance eleven]+[Attendance twelve]+[Attendance thirteen]+[Attendance fourteen])>2) AND (([Quarter 2].[First sick pay status(C17)])=True) AND (([Quarter 2].[Last sick pay status(C18)])=False) AND (([Quarter 2].[PHQ-9 S1])>10) AND (([Quarter 2].[GAD S1])>8));

Thanks in advance.

Hi

As a 'quick fix' you'd probably need to use the NZ() function

SELECT Nz([Attendance one], 0) + Nz([Attendance two],0) + Nz([Attendance three],0)............etc

For a more permanent fix, it looks like you could do with having a rethink of your table structure which at the moment doesn't seem to be normalised

Alan is a master of understatement. (IMG:style_emoticons/default/smile.gif)

Your table is not well designed. Here's some reading to help you get a better footing under your table design. It's work now, but it will pay off in a more flexible, efficient database.

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• MSKB 283878: Description of the database normalization basics
  
• Presentation and sample db
• Crystal's ACCESS Basics for Programming
  
• Roger Carlson’s Blog on Normalization

Thanks for the reply Alan, I have applied the Select Nz command however its still not showing the zero just blank. I am near the end of the project now and just finalising the reports with the querys. While I appreciate I could of made one or two of the tables better(next time), the only problem is that it isnt showing zero.

Is there anything else I can do? As I am concerned that if I go back to the tables, I might stuff up the many months I put into it.

Hi



As George said above " It's work now, but it will pay off in a more flexible, efficient database."

I totally understand that it means probably a major overhaul, but the benefits you'd reap would repay the effort over and over again (IMG:style_emoticons/default/wink.gif)

As to your problem, it's hard to say really. Are the values all numeric (in which case I'd have thought the Nz() function would have worked), or could there be some zero length strings in the mix somewhere along the line ?

It's getting more and more 'messy', overly complicated and convoluted, but you could try using IIf() instead of the Nz() to account for any ZLS

SELECT IIf(Len([Attendance one] & vbNullString) = 0, 0, [Attendance one]) + IIf(Len([Attendance two] & vbNullString) = 0, 0, [Attendance two])............etc

If they're text values, you may also need to coerce them to numeric ones.

Start by simplifying your query to only include a couple of the repeating fields, then adding them back one at a time till you find the first one that's causing your current problem. You may be able to find out if it's a ZLS that way

As you can see though, the problems are all caused by the basic structure being wrong and you'll keep on running into headscratchers and sleepless nights - plus having to throw more and more code at things to solve what would normally be very easy

Not to whip this dead horse too much further, but one of the many reasons we always encourage valid table design over "spreadsheet" table design is that the latter leads, almost inevitably and invariably, to the kind of problem you are having to sort out now. The point is not that you CAN'T do it, the point is that using a "spreadsheet" style table is almost always harder.

We fully understand that a working database is not easy to refactor, but there are times when you just need to say, "enough is enough" and just do it over.

In the meantime, follow Alan's suggestion and hope for the best.