UtterAccess Discussion Forums

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Sngglbear600

May 30 2008, 06:41 PM

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

May 30 2008, 07:13 PM

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

May 30 2008, 08:02 PM

Why don't you try

CODE

Where WorkCode=""

DougY

May 31 2008, 01:33 AM

It's not the same as Null... This FAQ will explain Nulls

Sngglbear600

Jun 2 2008, 05:32 PM

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.

DougY

Jun 2 2008, 07:54 PM

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;

Sngglbear600

Jun 4 2008, 04:11 PM

Yay, that worked!!!! Thanks!

DougY

Jun 4 2008, 04:21 PM

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.