Solved

Need results with a NULL field.

Posted on 2008-10-10
3
179 Views
Last Modified: 2008-11-15
I have a report that shows me labs my company has run for a specific time period. The labs are usually run for a surgeon but not always and thus the surgeon (tbldoctors.lastname) field is sometimes NULL. I need to get the results for a specific time period regardless of the fact that this field might be null so that we can see all labs performed. I am attaching the SQL statement created by Crystal below as a CODE SNIPPET

My results are as follows for 6/1/08 - 10/10/08
-1- If I remove the tbldoctors.lastname field, I get 20 labs for the time frame
-2- If I leave the tbldoctors.lastname field, I get 13 labs for the same time period

I tried the following formula field to try to get the NULL fields to return "n/a" but I get the same 13 results:

if isnull ({tblDoctors.LastName}) then "n/a" else
{tblDoctors.LastName}

Any help would be GREATLY APPRECIATED.
SELECT "tblCadaverLabs"."LabID", "tblLabLocations"."LocationName", "tblCadaverLabs"."LabDate", "tblMISUsers"."Name", "tblCadaverLabs"."LabStatus", "tblDoctors"."LastName"
 FROM   (((("ATR0605_003LIVE"."dbo"."tblCadaverLabs" "tblCadaverLabs" INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabLocations" "tblLabLocations" ON "tblCadaverLabs"."LocationID"="tblLabLocations"."LocationID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabSupport" "tblLabSupport" ON "tblCadaverLabs"."LabID"="tblLabSupport"."LabID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabDoctors" "tblLabDoctors" ON "tblCadaverLabs"."LabID"="tblLabDoctors"."LabID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblDoctors" "tblDoctors" ON "tblLabDoctors"."DoctorID"="tblDoctors"."DoctorID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblMISUsers" "tblMISUsers" ON "tblLabSupport"."UserID"="tblMISUsers"."UserID"
 WHERE  ("tblCadaverLabs"."LabDate">={ts '2008-06-01 00:00:00'} AND "tblCadaverLabs"."LabDate"<{ts '2008-10-11 00:00:00'}) AND "tblCadaverLabs"."LabStatus"='original'
 ORDER BY "tblCadaverLabs"."LabDate"

Open in new window

0
Comment
Question by:dsabine
3 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22693977
Try using a LEFT OUTER JOIN rather than inner joins.

A left outer join gets all the records in the left table tblCadaverLabs and all matching records and nulls from the other table.

mlmcc
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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