"Invalid use of Null" in Query Design

[eg_go]

Hello,

This is the first time I get this error when running/editing a query.
The strange thing is that the query runs, but after I have saved it, I
cannot edit it in Query Design view (I get this error), I can only
edit it in SQL View.

The SQL (simplified) is:

SELECT Claim.ClaimID, Source.Description, Claim.SourceID,
Source.SourceID
FROM Source RIGHT JOIN Claim ON Source.SourceID = Claim.SourceID
ORDER BY Claim.SourceID;

NOTE: Claim.SourceID is not required; Source.SourceID is key
autonumber.

The only thing I can think of is that Claim.SourceID may be null. But
why does the query run and give results (with null values), but cannot
be viewed in Query Design?...

The db was developed by someone else originally in Access97 and then
converted to 2002, and it has many tables and relationships among
them. I tried to reproduce this behaviour in a newly created db with
only two tables with the same definitions, but couldn't; the same kind
of query worked in this one.

Any idea would be appreciated.

Regards,
Gaby

 

[Allen Browne]

This should work fine, but here's some suggestions:

1, Make sure you have the Name AutoCorrect boxes unchecked under:
Tools | Options | General | Name AutoCorrect
Then compact the databaase. Explanation of why:
http://allenbrowne.com/bug-03.html

2. Try a left join instead of a right, i.e.:
FROM Claim LEFT JOIN Source ON Claim.SourceID = Source.SourceID

3. Enclose Description in square brackets (since tables, queries and fields
have a Description property), i.e.:
Source.[Description]

4. Does the problem only occur if you don't simplify the query down to the
example given? For example, is there a Yes/No field in the table from the
outer side of the join? If so, see:
http://allenbrowne.com/bug-14.html