Solved

I have a join Query that Returns Nulls. Is There a Way to Make it Return Zeroes instead of Nulls?

Posted on 2011-09-09
4
246 Views
Last Modified: 2012-05-12
I have a join query that returns nulls. (Code posted below)

I would like the nulls to show as zeroes instead of just empty values.

The QPR_Intersuhl_QNs_tbl is the table with the gaps that show up as nulls. I would like the Total_QMs field to return a zero if the value for that field is a null.
SELECT QPR_Interstuhl_QNs_tbl.WeekEndingDate, QPR_Interstuhl_QNs_tbl.Total_QNs, Possible_Week_Ending_Dates_Tbl.PossibleWeekEndingDates
FROM QPR_Interstuhl_QNs_tbl RIGHT JOIN Possible_Week_Ending_Dates_Tbl ON QPR_Interstuhl_QNs_tbl.WeekEndingDate = Possible_Week_Ending_Dates_Tbl.PossibleWeekEndingDates;

Open in new window

0
Comment
Question by:Rex85
  • 2
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 36513042
Wrap those other fields in the NZ() function.

SELECT NZ(QPR_Interstuhl_QNs_tbl.WeekEndingDate, 0), NZ(QPR_Interstuhl_QNs_tbl.Total_QNs, 0)

Although I'm not sure why you would want to display a zero for a blank date field.
0
 

Author Comment

by:Rex85
ID: 36513058
It's the one field ... Total QNs that needs to be a zero. It is essentially a count of complaints
0
 

Author Closing Comment

by:Rex85
ID: 36513069
that did it. thanks again! (I only used it on the one field.)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36513071
as recommended, use the NZ() function.  NZ stands for NullToZero.  By default it is supposed to return a zero if you don't pass it the optional value, but I generally provide it the 0 anyway.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

778 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question