Adding fields working for some records, not all

[S Himmelrich]

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

 

[Bob Barrows]

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) + ...

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

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/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/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!