Null Error

[Joy Rose]

I have a query that includes the field below:

LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)

I get the return message "Invalid use of Null." I don't understand?!

 

[Tom Ellison]

Dear Joy:

Perhaps one of the 3 columns used here is NULL in some rows.

If you temporarily remove this column and insert in its place 3
columns for [Quantity], [UnitPrice], and [Discount] and add the
criteria to test whether any of these 3 are NULL (IS NULL) then you
would quickly see if this is the case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Joy Rose]

Tom -- Following is the SQL without the Line Total field: This works, and
does not display the error "Invalid use of Null".

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate, Sum([Order Details].Quantity) AS
[Total Units], [Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Discount
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount;

When I have the Line Total field inserted - as follows -
SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total Sales],
Sum([Order Details].Quantity) AS [Total Units]
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate;

This query is to give me total sales by customer by invoice number. I would
prefer not seeing the customer name and invoice listed each line item on
each invoice -- only one time per invoice.

Thanks.

Dear Joy:

Perhaps one of the 3 columns used here is NULL in some rows.

If you temporarily remove this column and insert in its place 3
columns for [Quantity], [UnitPrice], and [Discount] and add the
criteria to test whether any of these 3 are NULL (IS NULL) then you
would quickly see if this is the case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query that includes the field below:

LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)

I get the return message "Invalid use of Null." I don't understand?!

 

[Tom Ellison]

Dear Joy:

If you build a report on this query, you can easily suppress the
repetition of these columns and just leave that area on the report
blank. This is one of the important features in reporting.

In a query these columns are not typically suppressed. The query
could be sorted by any set of columns and the repeating character of
these columns would not lend itself to suppression.

I could offer you some advanced techniques that would provide this
ability in a query. I sometimes do this myself. This technique
involves ranking the rows in groups so you can suppress repeating
values any time the rank based on that column's group is not 1 (not
the first row of a set). This is not only an advanced challenge to
build, but it is a fair performance hog. Using the Jet database
engine on a large set of data it may become impractical.

Using a report to prepare your results gives you considerable
flexibility to create levels of subtotals and totals. This is also
something that can be performed within a query, and it is occasionally
necessary to do so. But it is usually a lot of unnecessary work, and
runs faster done in the report.

In summary, I am recommending you perform totalling and suppression of
repeating values in a report, not in a query, at least until you come
to a situation where this is absolutely essential.

There are also situations where you are working with data like this in
a form. There, I recommend you use separate sub-forms that reflect
the normalized view of the data and make them interoperate according
to the relationships.

What this might mean in your situation is that Order table with its
Customer, OrderDate, SalesTaxRate, etc. would be represented in one
subform with only one line for each order. The user could select any
order from this, and the OrderDetails would appear as 1 or more lines
in a separate subform. This is an interactive way of seeing the same
thing in a way where the repetition of certain columns does not come
into play.

Does any of this make sense? I'm really trying to guide you toward
using the tools in the simplest manner to create an effective solution
for your needs. I have found from experience that the tools are
adequate to most any job, but that it is very important to see them
for what they are and not strain to make those tools work according to
some design you have imagined but for which they are not really
suited.

Anyway, I am getting that impression from what you asked. Sorry if
I've gotten off base. Just trying to help the best I know how!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom -- Following is the SQL without the Line Total field: This works, and
does not display the error "Invalid use of Null".

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate, Sum([Order Details].Quantity) AS
[Total Units], [Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Discount
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount;

When I have the Line Total field inserted - as follows -
SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total Sales],
Sum([Order Details].Quantity) AS [Total Units]
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate;

This query is to give me total sales by customer by invoice number. I would
prefer not seeing the customer name and invoice listed each line item on
each invoice -- only one time per invoice.

Thanks.

Dear Joy:

Perhaps one of the 3 columns used here is NULL in some rows.

If you temporarily remove this column and insert in its place 3
columns for [Quantity], [UnitPrice], and [Discount] and add the
criteria to test whether any of these 3 are NULL (IS NULL) then you
would quickly see if this is the case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query that includes the field below:

LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)

I get the return message "Invalid use of Null." I don't understand?!