Prev: In crosstab query, how do I count not including duplicates?
Next: Trim Time off Date
From: Boon on 25 Feb 2010 12:31
Hi,

I have a table A. Table A has 2 fields - Sale1 and Sale2. I want to create a
query that will sum the value in Sale1 and Sale2. In the query I wrote --->
Output: [Sale1]+[Sale2]

The problem I have is that if either value in Sale1 or Sale2 is null, the
Output return Null value. But I want it to show the value and treat null as
0. For instance, 5+Null =5.

thanks,
Boon


From: fredg on 25 Feb 2010 12:51
On Thu, 25 Feb 2010 11:31:11 -0600, Boon wrote:

> Hi,
>
> I have a table A. Table A has 2 fields - Sale1 and Sale2. I want to create a
> query that will sum the value in Sale1 and Sale2. In the query I wrote --->
> Output: [Sale1]+[Sale2]
>
> The problem I have is that if either value in Sale1 or Sale2 is null, the
> Output return Null value. But I want it to show the value and treat null as
> 0. For instance, 5+Null =5.
>
> thanks,
> Boon

Look up the Nz function in VBA help:
Output:Nz([Sale1],0) + Nz([Sale2],0)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: KARL DEWEY on 25 Feb 2010 13:15
Output: Nz([Sale1],0) + Nz([Sale2],0)

--
Build a little, test a little.


"Boon" wrote:

> Hi,
>
> I have a table A. Table A has 2 fields - Sale1 and Sale2. I want to create a
> query that will sum the value in Sale1 and Sale2. In the query I wrote --->
> Output: [Sale1]+[Sale2]
>
> The problem I have is that if either value in Sale1 or Sale2 is null, the
> Output return Null value. But I want it to show the value and treat null as
> 0. For instance, 5+Null =5.
>
> thanks,
> Boon
>
>
> .
>
 | 
Pages: 1
Prev: In crosstab query, how do I count not including duplicates?
Next: Trim Time off Date