Unable to sum new + old columns (of like data)

  • Thread starter Thread starter teh_chucksta
  • Start date Start date
T

teh_chucksta

Hi and thanks for helping,

I created 2 new columns today (currency format) in my Access DB with new
data (i.e. not available before and so historical data isn't updated as well)
but after including them in a build formula to sum, the historical data
doesn't compute. Here's what I mean:

field1 - on running data
field2 - on running data
field 3 - on running data
new field4 - new add'l data going forward
new field5 - new add'l data going forward

I want to add the five fields together for past time periods when the new
field values should be 0 (or null) but Access won't let me. I hope this
posting isn't too cyrptic.

Thanks,
Charlie
 
Hi and thanks for helping,

I created 2 new columns today (currency format) in my Access DB with new
data (i.e. not available before and so historical data isn't updated as well)
but after including them in a build formula to sum, the historical data
doesn't compute. Here's what I mean:

field1 - on running data
field2 - on running data
field 3 - on running data
new field4 - new add'l data going forward
new field5 - new add'l data going forward

I want to add the five fields together for past time periods when the new
field values should be 0 (or null) but Access won't let me. I hope this
posting isn't too cyrptic.
Click to expand...

It is.

"Won't let me" means nothing. Do you get a wrong answer, no answer, an error
message...!? What "fuild formula" did you use? What "doesn't compute"?

I'm GUESSING that you need to use the NZ() function to have Null To Zero
somewhere in your formula, but since I can't see the formula...
 
Null to Zero sounds right. Can you please create a mock formula please.

Thanks John,
Charlie
 
Null to Zero sounds right. Can you please create a mock formula please.
Click to expand...

SumOfFields: NZ([field1]) + NZ([field2]) + NZ([field3]) + NZ([field4]) +
NZ([field5])

If your fields actually ARE named field1 etc., I'd really suggest that you
rename them to something meaningful. It's legal to use blanks and
punctuation... but don't; use CamelCase instead so the name is
human-meaningful but not going to mess up Access. For instance you might have
fieldnames such as

LastName State UserID StartingBalance
 
To everyone, thank you. The NZ function is easy, intuitive and worked great.

John W. Vinson said:
Null to Zero sounds right. Can you please create a mock formula please.
Click to expand...

SumOfFields: NZ([field1]) + NZ([field2]) + NZ([field3]) + NZ([field4]) +
NZ([field5])

If your fields actually ARE named field1 etc., I'd really suggest that you
rename them to something meaningful. It's legal to use blanks and
punctuation... but don't; use CamelCase instead so the name is
human-meaningful but not going to mess up Access. For instance you might have
fieldnames such as

LastName State UserID StartingBalance
Click to expand...
 
Back
Top