Invalid use of Null (You'll be sent to Guantanamo Bay!)

[Claude]

I have a query containing 3 sub (grouped) queries and 1 Customers table. The
subs are: Invoices, Payments, Credits. From Customers, there are 1209 records
of field "BF Total". Fields from Invoices indicate 595 records, Payments 30
records, Credits 20 records. When I connect a Join between Customers, CustID
to Invoices Grouped query to show all records, they count 1209. (The
"Freight" field from Invoices Grouped shows as "Error" but the query runs.)
When I change the join to equal, then it returns 595, no field displayed as
"Error". Problem: When this query is changed to Sum, with a join showing all
records from Customers, then I get "Invalid use of Null". (Remember, the
object of this query is simple: to show the BF Total of all Customers, then
to append where matches are found, all Invoices, Payments and Credits for the
specific customer). Please send me a (Human) wizard!

 

[Dale Fye]

Claude,

Post your SQL so we can see it.

My guess is that the Error is coming up in a computed field that needs a
value from a field that contains nulls when you use an outer join, but which
contains valid information when you use an inner join.

Dale

 

[Claude]

Hi Dale, whether I get a solution or not, I think this is man kind of you to
take the time... THANKS!.

Here's the SQL of the troubling query that returns "Invalid use of Null":-

SELECT [Invoices Grouped].[Invoice No], [Invoices Grouped].CustomerID,
[Invoices Grouped].ShipName, [Invoices Grouped].ExtendedPrice, [Invoices
Grouped].Freight, [Invoices Grouped].B4VAT, [Invoices Grouped].VAT, [Invoices
Grouped].InvoiceAmount
FROM Customers LEFT JOIN (([Credits Grouped] RIGHT JOIN [Invoices Grouped]
ON [Credits Grouped].CustomerID = [Invoices Grouped].CustomerID) LEFT JOIN
[Payments Grouped] ON [Invoices Grouped].CustomerID = [Payments
Grouped].CustomerID) ON Customers.CustomerID = [Invoices Grouped].CustomerID
GROUP BY [Invoices Grouped].[Invoice No], [Invoices Grouped].CustomerID,
[Invoices Grouped].ShipName, [Invoices Grouped].ExtendedPrice, [Invoices
Grouped].Freight, [Invoices Grouped].B4VAT, [Invoices Grouped].VAT, [Invoices
Grouped].InvoiceAmount
ORDER BY [Invoices Grouped].ShipName;

 

[Dale Fye]

Claude,

I assume that [Invoices Grouped] is a query? Does the [Freight] field give
you an error when you run that query by itself?

Have you tried rebuilding this query one table/query at a time to see at
which point the error starts showing up?

Hi Dale, whether I get a solution or not, I think this is man kind of you to
take the time... THANKS!.

Here's the SQL of the troubling query that returns "Invalid use of Null":-

SELECT [Invoices Grouped].[Invoice No], [Invoices Grouped].CustomerID,
[Invoices Grouped].ShipName, [Invoices Grouped].ExtendedPrice, [Invoices
Grouped].Freight, [Invoices Grouped].B4VAT, [Invoices Grouped].VAT, [Invoices
Grouped].InvoiceAmount
FROM Customers LEFT JOIN (([Credits Grouped] RIGHT JOIN [Invoices Grouped]
ON [Credits Grouped].CustomerID = [Invoices Grouped].CustomerID) LEFT JOIN
[Payments Grouped] ON [Invoices Grouped].CustomerID = [Payments
Grouped].CustomerID) ON Customers.CustomerID = [Invoices Grouped].CustomerID
GROUP BY [Invoices Grouped].[Invoice No], [Invoices Grouped].CustomerID,
[Invoices Grouped].ShipName, [Invoices Grouped].ExtendedPrice, [Invoices
Grouped].Freight, [Invoices Grouped].B4VAT, [Invoices Grouped].VAT, [Invoices
Grouped].InvoiceAmount
ORDER BY [Invoices Grouped].ShipName;

I have a query containing 3 sub (grouped) queries and 1 Customers table. The
subs are: Invoices, Payments, Credits. From Customers, there are 1209 records
of field "BF Total". Fields from Invoices indicate 595 records, Payments 30
records, Credits 20 records. When I connect a Join between Customers, CustID
to Invoices Grouped query to show all records, they count 1209. (The
"Freight" field from Invoices Grouped shows as "Error" but the query runs.)
When I change the join to equal, then it returns 595, no field displayed as
"Error". Problem: When this query is changed to Sum, with a join showing all
records from Customers, then I get "Invalid use of Null". (Remember, the
object of this query is simple: to show the BF Total of all Customers, then
to append where matches are found, all Invoices, Payments and Credits for the
specific customer). Please send me a (Human) wizard!