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

[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

 

[John W. Vinson]

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.

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

 

[teh_chucksta]

Null to Zero sounds right. Can you please create a mock formula please.

Thanks John,
Charlie

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Nz(Field1,0) + Nz(Field2,0) + Nz(Field3,0) + .... etc. ....

If the Field is Null zero is substituted in the addition.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBST7aloechKqOuFEgEQI4iQCg/QHqCb+bJBAyeHXi57I+3qVtX4UAn0Pr
l4YZYCyty1Y29Tt8MTjqzQOY
=bLbt
-----END PGP SIGNATURE-----

 

[John W. Vinson]

Null to Zero sounds right. Can you please create a mock formula please.

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

 

[teh_chucksta]

To everyone, thank you. The NZ function is easy, intuitive and worked great.

Null to Zero sounds right. Can you please create a mock formula please.

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