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
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 48

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 48

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

729 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