Windows Forum·65,800 discussions

Replacing Null values with 0 for an expr

Locked

In an access I have a query that uses the values from several other sum queries. It is an inventory query which shows such things as items in stock(total delivered – total sent out) and items on order (total ordered – total delivered). Using the join properties the query will show results for each item even if there are no results for it in some of the sum fields. The problem is that with these fields with no results (null valuse) no value is shown in the expression. For example: 20 desks have been ordered but none have yet been delivered (a null value for total delivered). The on order expression shows nothing rather than 20 as it should. I am sure there must be some way to replace all null values in an expression with zeros.

Thanks for any help.
Mark

Topic

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

One way to do this is using the Nz() string function. The idea is this, you can set a string variable up to return the value you need. X, for instance can be this value. If X comes back equal to null, this can cause problems in fields defined to disallow null values. The Nz function works like this: x=Nz(x,”0″) ..if x comes back as a null then 0 is reported within the cells. Let me know if this helps you.

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Got me on the right track.

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

The previous answer is correct if your problem is calculating on nulls. However, it sounds like you don’t have the join syntax correct. The correct format is below:

SELECT Inv.item_name, Stock.tot_ord, Stock.tot_del
FROM Stock RIGHT JOIN Inv ON Stock.ID = Inv.ID
GROUP BY Inv.item_name

It is important to have the list of items on the side with the outer join, in this case on the Right. This will ensure that all items in Inv are reported, even if they are zero.

Chris

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Thanks but the join properties were right.
eg item instock onorder
item1 50 5
item2

if the join properties were not right item2 wouldn’t appear at all. The problem was that the queries that give the results were coming up with nothing as the item2 doesn’t have values for some of the fields they need.

Thanks anyway.

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Use the Isnull function for that part of the query.

IIF(Isnull(),0,)) as somefield

Where field is the name of the field you’re getting nulls in.

This will put 0’s where the field is null and the value of the field where it exists.

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Fantastic.
Slipped it straight into the expression.
Thanks a lot.

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Sounds like you need to control what is (or is not being returned in the query). The easy way to do this is by combining the Immediate If and IsNull functions. Use IIF(IsNull([Field]),”0″,[Field]) A variation on this is the IsEmpty function that works on form controls. If you have trouble (data type mismatch) you might try replacing “0” with the Ascii value function for 0: CHR(48)

Replacing Null values with 0 for an expr

In reply to Replacing Null values with 0 for an expr

Fantastic.
Slipped it straight into the expression.
Thanks a lot.

[Author]

Replies