Full Version: Null Values
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Sngglbear600
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!
Steve Schapel
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?
hdlee
Why don't you try
CODE
Where WorkCode=""
DougY
It's not the same as Null... This FAQ will explain Nulls
Sngglbear600
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
Ordere 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.
DougY
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 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;
Sngglbear600
Yay, that worked!!!! Thanks!
DougY
You are welcome. Glad we could help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.