I have a query with the following syntax:

FY12 OPEX Forecast: Format([2012 Q1 OPEX Forecast Cost]+[2012 Q2 OPEX
Forecast Cost]+[2012 Q3 OPEX Forecast Cost]+[2012 Q4 OPEX Forecast
Cost],"Currency")

My results add in some rows, but not all - any ideas why this may be
happening?

Thank you in advance for any help
Scott

S Himmelrich wrote:
> I have a query with the following syntax:
>
> FY12 OPEX Forecast: Format([2012 Q1 OPEX Forecast Cost]+[2012 Q2 OPEX
> Forecast Cost]+[2012 Q3 OPEX Forecast Cost]+[2012 Q4 OPEX Forecast
> Cost],"Currency")
>
> My results add in some rows, but not all - any ideas why this may be
> happening?
>
> Thank you in advance for any help
> Scott

I'm not sure what you mean by "not working" (it always helps to describe
your symptoms without using generic terms such as "no luck" or "doesn't
work), but I suspect some of your records contain nulls in those fields. Use
the Nz function to replace nulls with zero in your expression.:

.... (Nz([2012 Q1 OPEX Forecast Cost],0) + ...

On Mon, 19 Dec 2011 08:46:01 -0800 (PST), S Himmelrich <(E-Mail Removed)>
wrote:

>I have a query with the following syntax:
>
>FY12 OPEX Forecast: Format([2012 Q1 OPEX Forecast Cost]+[2012 Q2 OPEX
>Forecast Cost]+[2012 Q3 OPEX Forecast Cost]+[2012 Q4 OPEX Forecast
>Cost],"Currency")
>
>My results add in some rows, but not all - any ideas why this may be
>happening?
>
>Thank you in advance for any help
>Scott

If any one of the Cost fields is NULL, the entire sum will be NULL. To treat
NULL values as zeros, you can use the NZ() function:

FY12 OPEX Forecast: Format(NZ([2012 Q1 OPEX Forecast Cost]) +
NZ([2012 Q2 OPEX Forecast Cost])+ NZ([2012 Q3 OPEX Forecast Cost])+
NZ([2012 Q4 OPEX Forecast Cost]),"Currency")

I hope these fields are calculated in a query, or directly from a spreadsheet;
if they are fields in a Table you need to correct your normalization. Storing
data (dates) in fieldnames has no place in a relational database!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Dec 19, 12:16*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Mon, 19 Dec 2011 08:46:01 -0800 (PST), S Himmelrich <himmelr...@gmail.com>
> wrote:
>
> >I have a query with the following syntax:
>
> >FY12 OPEX Forecast: Format([2012 Q1 OPEX Forecast Cost]+[2012 Q2 OPEX
> >Forecast Cost]+[2012 Q3 OPEX Forecast Cost]+[2012 Q4 OPEX Forecast
> >Cost],"Currency")
>
> >My results add in some rows, but not all - any ideas why this may be
> >happening?
>
> >Thank you in advance for any help
> >Scott
>
> If any one of the Cost fields is NULL, the entire sum will be NULL. To treat
> NULL values as zeros, you can use the NZ() function:
>
> FY12 OPEX Forecast: Format(NZ([2012 Q1 OPEX Forecast Cost]) +
> NZ([2012 Q2 OPEX Forecast Cost])+ NZ([2012 Q3 OPEX Forecast Cost])+
> NZ([2012 Q4 OPEX Forecast Cost]),"Currency")
>
> I hope these fields are calculated in a query, or directly from a spreadsheet;
> if they are fields in a Table you need to correct your normalization. Storing
> data (dates) in fieldnames has no place in a relational database!
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

Thank you very much! This resolved the issue I was having. A good to
know going forward. Happy Holidays!