Adding fields working for some records, not all

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

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
 
S said:
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
Click to expand...

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) + ...
 
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
Click to expand...

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
 
I have a query with the following syntax:
Click to expand...
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")
Click to expand...
My results add in some rows, but not all - any ideas why this may be
happening?
Click to expand...
Thank you in advance for any help
Scott
Click to expand...

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
Click to expand...

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