Return null / zero length / nothing

[317342]

Hi everyone,

After seaching for a few days, I still can't understand how to
implement nulls / zero length.

I have a table called sales recording all sales records. Now I want to
run some queries:

1: select stuffs sold in the last 7 days. List their ID, blah blah, and
price of each item sold at. The field is called "price"

2: sum the price of all items using query #1 (above)

Problems: If nothing had been sold in the last 7 days ......

1: will return nothing. I want it to return $0

2: sum will return nothing. Of course I want it to return $0 as well.

I know there are NZ & IIF functions. With query 1, I've tried Expr1:
Nz([price],0). It still returns me blank field. In order to figure what
Access is returning me, I tried Expr1: IIf([price] Is
Null,"stupied",[price]), which returns me blank fields. I then tried
Expr1: IIf([price]="","stupied",[price]), and it is still giving me
blank fields.

What else can I try? Am I going the wrong direction?

 

[Allen Browne]

You cannot compare anything to Null, i.e. the expression:
If xx = Null ...
will never be true. Instead use:
If IsNull(xx) ...
Exaplanation of why:
http://allenbrowne.com/casu-12.html

To get the value of products sold in the last 7 days per product, the query
will depend on your data structure. To use this example
1. Open the Northwind sample database. (It installed on your disk with
Access.)
2. Create a new query. Close the AddTable dialog.
3. Switch to SQL View (View menu).
4. Paste in this statement:

SELECT [Order Details].ProductID,
Sum([Quantity]*[UnitPrice]*(1-[Discount])) AS Amount
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=Date()-7))
GROUP BY [Order Details].ProductID;

5. Switch back to design view.

 

[Steve Schapel]

317342,

It is not clear exactly what you are doing. Putting a calculated
field in a query, such as those you have suggested, will result in
that calculation being done for each record as returned by the query.
If there are no records, then you get the calculated field for no
records. The question of zero or null doesn't come into it at all.

But if you are doing the totalling based directly on the table, you
can get it to show 0 when there are no sales by this type of query...
SELECT Val(Nz(Sum([Price]),0)) AS TotalPrice
FROM Sales
WHERE SaleDate>Date()-7

- Steve Schapel, Microsoft Access MVP

 

[317342]

Ok, I think because query 1 is not returning anything, query 2 is
returning nothing. I'm guessing it's not null, not "", but it's "no
data"?

 

[Steve Schapel]

Yes, that's correct.

- Steve Schapel, Microsoft Access MVP

 

[317342]

"It is not clear exactly what you are doing."

I want to create a statement for sales in the last 7 days. If there ar
no sales, I want to return $0, not returning nothing.

"Putting a calculated field in a query, such as those you hav
suggested, will result in that calculation being done for each recor
as returned by the query."

Right

"But if you are doing the totalling based directly on the table, you
can get it to show 0 when there are no sales by this type of query...
SELECT Val(Nz(Sum([Price]),0)) AS TotalPrice
FROM Sales
WHERE SaleDate>Date()-7"

Now, this actually answers my questions. Thanks a lo