SQL Query with Nulls

  • Thread starter Thread starter Roger Twomey
  • Start date Start date
R

Roger Twomey

I am not very familiar with SQL in Microsoft access. In Sql server I can do
this:

select isNull(InvoiceTotal, 0)
From Invoices
Where InvoiceID = 1234

This returns the value of InvoiceTotal if it is not null, or 0 if it is
null.

How can I do this with Microsoft Access?

(The query I need this for is a lot more complicated than the sample but it
will do for now).

Thanks.
 
Access has the Nz() function that converts Nulls to a different value.

Nz(var, "Hello")
Nz(var, 999)
Nz(var, #12/12/2004#)
 
I am not very familiar with SQL in Microsoft access. In Sql server I
can do this:

select isNull(InvoiceTotal, 0)
From Invoices
Where InvoiceID = 1234
Click to expand...
<snip>

Your query in Access could be:

Select (nz(Invoicetotal)+0) AS InvoiceTotal
From Invoices
Where InvoiceID = 1234

I just did a whole whack of these earlier today, the "AS InvoiceTotal" is
optional, but if you do not put it Access will assign a generic expression
ID like "AS Expr001".
 
Thank you!

That is just what I was looking for.

I am curious, nz? Null Zebras? What does that stand for I wonder? Not very
intuitive!

Thanks again!
 
NZ -> Null to Zero

the default value for this function is zero, so if you just type
NZ([yourField]), it should return a zero if you don't pass it a value.
However, for ease of reading, and because I have occasionally gotten some
screwy results when I don't put the value in, I generally write it as
NZ([yourField], 0).

HTH
Dale
 
Back
Top