If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Invalid use of Null" in query
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 |
#2
|
|||
|
|||
"Invalid use of Null" in query
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" wrote in message ... 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 |
#3
|
|||
|
|||
"Invalid use of Null" in query
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 On Dec 10, 11:33*am, "Jeff Boyce" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
"Invalid use of Null" in query
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. "Rob Parker" wrote in message ... 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 On Dec 10, 11:33 am, "Jeff Boyce" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|