UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Null Values    
 
   
Sngglbear600
post May 30 2008, 06:41 PM
Post #1

UtterAccess Member
Posts: 23
Hi,

I am designing a query for a form that has 2 fields that automatically fill in data (ProjectID and WorkCodeID). WorkCodeID is not a required field so there are null values. When I run the query it excludes all records where this field is null. I have tried including WHERE WorkCodeID IS NULL in SQL but this worked the opposite (excluding all records). Any suggestions?

Thanks!
Go to the top of the page
 
+
Steve Schapel
post May 30 2008, 07:13 PM
Post #2

UtterAccess VIP
Posts: 3,881
From: New Zealand
Sngglbear,

Can you please go to the design view of the query, select SQL from the View menu, and copy/paste the SQL view of the query into your reply here? Thanks, that will help us to see what you're doing. It sounds like you have a Join in the query with another table based on this WorkCodeID field... Yes?
Go to the top of the page
 
+
hdlee
post May 30 2008, 08:02 PM
Post #3

UtterAccess Addict
Posts: 215
From: New York
Why don't you try
CODE
Where WorkCode=""
Go to the top of the page
 
+
DougY
post May 31 2008, 01:33 AM
Post #4

Utterly Abby-Normal
Posts: 9,781
From: Seattle, WA [USA]
It's not the same as Null... This FAQ will explain Nulls
Go to the top of the page
 
+
Sngglbear600
post Jun 2 2008, 05:32 PM
Post #5

UtterAccess Member
Posts: 23
Here is the SQL for my query,

SELECT [Time Card Hours].TimeCardID, [Time Card Hours].ProjectID, Projects.ProjectName, [Time Card Hours].WorkCodeID, [Work Codes].WorkCode, [Time Card Hours].BillableHours, [Time Card Hours].BillingRate

FROM [Work Codes] INNER JOIN (Projects INNER JOIN [Time Card Hours] ON Projects.ProjectID=[Time Card Hours].ProjectID) ON [Work Codes].WorkCodeID=[Time Card Hours].WorkCodeID

ORDER BY [Time Card Hours].TimeCardID;

Thanks for the help.
Go to the top of the page
 
+
DougY
post Jun 2 2008, 07:54 PM
Post #6

Utterly Abby-Normal
Posts: 9,781
From: Seattle, WA [USA]
Change the INNER JOIN to RIGHT JOIN (you'll get everything from Projects, and the patching records from Work Codes... the INNER JOIN excludes the null -- you can not join on a null...

Air Code -- Not Tested

CODE
SELECT    [Time Card Hours].TimeCardID,
    [Time Card Hours].ProjectID,
    Projects.ProjectName,
    [Time Card Hours].WorkCodeID,
    [Work Codes].WorkCode,
    [Time Card Hours].BillableHours,
    [Time Card Hours].BillingRate

FROM    [Work Codes]
RIGHT JOIN
    (Projects
    INNER JOIN
        [Time Card Hours]
    ON    Projects.ProjectID=[Time Card Hours].ProjectID)
ON    [Work Codes].WorkCodeID=[Time Card Hours].WorkCodeID

WHERE    [Time Card Hours].WorkCodeID IS NULL

ORDER BY
    [Time Card Hours].TimeCardID;
Go to the top of the page
 
+
Sngglbear600
post Jun 4 2008, 04:11 PM
Post #7

UtterAccess Member
Posts: 23
Yay, that worked!!!! Thanks!
Go to the top of the page
 
+
DougY
post Jun 4 2008, 04:21 PM
Post #8

Utterly Abby-Normal
Posts: 9,781
From: Seattle, WA [USA]
You are welcome. Glad we could help.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th June 2013 - 10:13 PM