Replace null with "0" in expression

[Guest]

Hi!
I'm making a query where an expression calculates a total price of an order.
Total: Sum([Amount]*[Price])
is the expression I'm using, but when I run this it does not list orders
with 0 items (yes, I know, this isn't supposed to happen, but I want it
anyway).

I tried searching through the help file and found that
=OOM(IsNull([Region]),[Stad]&" "& [Postnummer], [Stad]&" "&[Region]&"
"&[Postnummer])
is supposed to list [Stad]&" "&[Region]&" "&[Postnummer] if [Region] has a
value and [Stad]&" "& [Postnummer] if [Region] is null (from the helpfile of
the swedish version).

I used this as a reference and tried with this expression:
=OOM(IsNull ([Amount]),"0",(Sum([Amount]*[Price]))
and hoped that this would replace any null value with "0". But Access
complained that there was an error in the syntax and pointed to the first
comma. I then pasted the original example as the expression, and hoped to
work my way from there, but I got the same syntax error. Strange that the
help file would have syntax errors O_o

Anyway, is there anyone that knows the proper syntax or have a different
solution? Without any sql code, as I'm a complete n00b
Thanks!

 

[Keith Leslein]

Hello,


If you are trying to display a 0 for the null value in total you can
use:

Total: NZ(Sum([Amount]*[Price]),0)

This will display a 0 in the field if either value is null or 0 in the
[Amount]*[Price] calculation.

Hope this helps!

 

[[MVP] S.Clark]

Sometimes Null isn't really null. It can be a zero length string.
Try:
=OOM(Len(nz([Region],"")=0),[Stad]&" "& [Postnummer], [Stad]&" "&[Region]&"
"&[Postnummer])

 

[John Vinson]

Hi!
I'm making a query where an expression calculates a total price of an order.
Total: Sum([Amount]*[Price])
is the expression I'm using, but when I run this it does not list orders
with 0 items (yes, I know, this isn't supposed to happen, but I want it
anyway).

I tried searching through the help file and found that
=OOM(IsNull([Region]),[Stad]&" "& [Postnummer], [Stad]&" "&[Region]&"
"&[Postnummer])

I'm guessing that OOM is the (German? what version of Access are you
using?) equivalent of IIF?

I doubt that the NULL value of region is changing the list of orders
retrieved. If Region is NULL, then [Stad] & " " & [Region] & " " &
[Postnummer] would return [Stad], two consecutive blanks, and
[Postnummer]. The missing records are arising from some other problem
with the query; could you post the SQL view of the query itself? You
may need an OUTER JOIN to include records even if there are no
matching records in the order-details.

John W. Vinson[MVP]

 

[Guest]

Everyone, thanks for answering!

Keith Leslein and [MVP] S.Clark - I had high hopes for the NZ-thing, but
again Access complained about the comma. Aparently it's impossible to write a
proper syntax.

John Vinson - yes, you are completley right. OOM is the same as IIF. I'm
using the swedish version. What's an "outer join" and how do I use it?

I've uploaded a screenshot of the query to
http://www.kustfilm.com/matti/bildr/qry.jpg
"Inquiry" is where orders are held. It's the last expression that makes
things difficult. At its present state, orders/inquieries that doesn't
contain any instruments, i.e. the inquiries that don't appear in the
InstrumentInquiry table, won't show up in the query. I hope you can figure
this out even though its a foreign version.

Thanks a lot!

 

[John Vinson]

I've uploaded a screenshot of the query to
http://www.kustfilm.com/matti/bildr/qry.jpg
"Inquiry" is where orders are held. It's the last expression that makes
things difficult. At its present state, orders/inquieries that doesn't
contain any instruments, i.e. the inquiries that don't appear in the
InstrumentInquiry table, won't show up in the query. I hope you can figure
this out even though its a foreign version.

It's really simpler to open the Query in SQL view and post the SQL
text here - it may look like gibberish, but it's the *real* query and
quite understandable to those familiar with it.

That said... click on the Join line between tblInquiry and
tblInstrumentInquiry. Choose option 2 (or maybe option 3) - the
English is "Show all records in tblInquiry and matching records in
tblInstrumentInquiry". Save the query and try again. This should show
all records in tblInquiry, regardless of whether records exist in the
other table.

John W. Vinson[MVP]

 

[Guest]

Thank you John!
Now it works, and even without any sort of complicated code or expression to
set it up! Dealing with sql code is a bit out of my league.
Thanks again!