"Invalid use of Null" in query

[Rob Parker]

I have a query which has suddenly started giving this error. It is
based on two other queries, each of which run OK. The query contains
4 left joins (I'm wanting to return a complete set of records from the
left query), and the left query has no nulls in any field.

The SQL of the query is:
SELECT qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period, Sum(qryStaffEffortOverhead.Hours)
AS TotalHours, Sum(qryStaffEffortOverhead.CostOH) AS TotalCostOH
FROM qryStaffEffortFYWBSDivPeriod LEFT JOIN qryStaffEffortOverhead ON
(qryStaffEffortFYWBSDivPeriod.Period = qryStaffEffortOverhead.Period)
AND (qryStaffEffortFYWBSDivPeriod.DivID =
qryStaffEffortOverhead.DivID) AND (qryStaffEffortFYWBSDivPeriod.WBS =
qryStaffEffortOverhead.WBS) AND (qryStaffEffortFYWBSDivPeriod.FY =
qryStaffEffortOverhead.FY)
GROUP BY qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period;

Removing the joins one by one shows that the problem is caused by the
join on the Period field. The query on the right does not have
records for every period. But that shouldn't matter - I'm using the
standard technique for returning a complete set of records from the
left table; the LEFT JOIN should take care of that. For what it's
worth, there are no records in the right query with data in three of
the join fields, and Null in the Period field.

What's the problem here?

TIA,

Rob

 

[Jeff Boyce]

Rob

The implication of the error message is that there's a Null in your data
set, and Access can't figure out how to handle it.

Have you tried using queries on the individual tables, searching for records
containing a Null in each of those fields?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Rob Parker]

Hi Jeff,

The underlying table for the query on the right side of the join which
contains Period data is tblStaffEffort, and there are no records with
a Null.

The underlying table for the query on the left side of the join is
tblPeriods, which contains a single field, containing each of the
possible periods (1 to 27 - the number of fortnightly pay periods in a
calendar year). Again, no null record.

I realize what the error message implies, but it can't be the case.
And, as I say, this query used to run OK. I've compacted/repaired,
with no effect. Even decompiled (though this seemed highly unlikely
to have any effect, and it didn't).

I've just rebuilt the query from scratch, using the same two existing
queries, and the new one runs fine. Seems that it must have been a
corruption issue, which a compact/repair could not fix.

Rob

 

[Jeff Boyce]

Rob

I would have handled it the same way ...

Start with the most obvious/highest probability -- somewhere there's a Null.

If not that, next most likely (in this situation) is some subtle
corruption -- try rebuilding the query.

Congratulations!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff,

The underlying table for the query on the right side of the join which
contains Period data is tblStaffEffort, and there are no records with
a Null.

The underlying table for the query on the left side of the join is
tblPeriods, which contains a single field, containing each of the
possible periods (1 to 27 - the number of fortnightly pay periods in a
calendar year). Again, no null record.

I realize what the error message implies, but it can't be the case.
And, as I say, this query used to run OK. I've compacted/repaired,
with no effect. Even decompiled (though this seemed highly unlikely
to have any effect, and it didn't).

I've just rebuilt the query from scratch, using the same two existing
queries, and the new one runs fine. Seems that it must have been a
corruption issue, which a compact/repair could not fix.

Rob