Summing when one value is NULL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang Up /
Hoax / Nuisance])

TIA
 
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth
 
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


tina said:
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Sue Compelling said:
Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
 
you're welcome, glad it worked for you. take a look at the Nz Function topic
in Access Help, it'll tell you all about it. :)


Sue Compelling said:
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


tina said:
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type
Click to expand...
for
the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise'
Click to expand...
the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
You might also take a look at some information about normalization. Your
field names are "data values". Normalizing tables creates much more
flexibility in an application.

For instance, in a system like you have, what would you do if you wanted to
track calls of "Heavy breathing"? I assume you would need to create a field
in a table and then modify forms, queries, expression (note the solution to
this question), reports,....

Jeff Conrad has several links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101. He has
a newer site but I haven't favoritized it yet on this PC (sorry Jeff).

--
Duane Hookom
MS Access MVP


tina said:
you're welcome, glad it worked for you. take a look at the Nz Function
topic
in Access Help, it'll tell you all about it. :)


message
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


tina said:
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for
the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang
Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
Sue Compelling

This is definition of NZ Function, This is not the proper way to tell
someone to search in help. This is the best way, I assume. This is Microsoft
way to exaplin thing in incomplete format.

Nadeem

Nz Function
You can use the Nz function to return zero, a zero-length string (" "), or
another specified value when a Variant is Null. For example, you can use this
function to convert a Null value to another value and prevent it from
propagating through an expression.

Nz(variant, [valueifnull])

=(Nz([211 Service Enquiries], 0)

In your case u are telling this function that return zero in case ([211
Service Enquiries] is null value.

U can also modify this function in case u want return some Text value when u
donot want to use this function in any formula like

=(Nz([211 Service Enquiries], " Your Value is Null")

Thanks

Sue Compelling said:
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


tina said:
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Sue Compelling said:
Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
Jeff has a newer site? hopefully he or somebody else will see this thread
and post the link! :)

btw, thanks for picking up on the table design issue, Duane - i should have
addressed it, but was obviously asleep at the wheel when i answered this
one! <g>


Duane Hookom said:
You might also take a look at some information about normalization. Your
field names are "data values". Normalizing tables creates much more
flexibility in an application.

For instance, in a system like you have, what would you do if you wanted to
track calls of "Heavy breathing"? I assume you would need to create a field
in a table and then modify forms, queries, expression (note the solution to
this question), reports,....

Jeff Conrad has several links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101. He has
a newer site but I haven't favoritized it yet on this PC (sorry Jeff).

--
Duane Hookom
MS Access MVP


tina said:
you're welcome, glad it worked for you. take a look at the Nz Function
topic
in Access Help, it'll tell you all about it. :)


message
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


:

try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Hi

I have the following formula, which works when all fields for a
Click to expand...
date
range
are filled in. However, if there were no calls of a particular
Click to expand...
type
for
the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang
Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
Check this out
http://home.bendbroadband.com/conradsystems/accessjunkie.html.

--
Duane Hookom
MS Access MVP


tina said:
Jeff has a newer site? hopefully he or somebody else will see this thread
and post the link! :)

btw, thanks for picking up on the table design issue, Duane - i should
have
addressed it, but was obviously asleep at the wheel when i answered this
one! <g>


Duane Hookom said:
You might also take a look at some information about normalization. Your
field names are "data values". Normalizing tables creates much more
flexibility in an application.

For instance, in a system like you have, what would you do if you wanted to
track calls of "Heavy breathing"? I assume you would need to create a field
in a table and then modify forms, queries, expression (note the solution to
this question), reports,....

Jeff Conrad has several links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101. He has
a newer site but I haven't favoritized it yet on this PC (sorry Jeff).

--
Duane Hookom
MS Access MVP


tina said:
you're welcome, glad it worked for you. take a look at the Nz Function
topic
in Access Help, it'll tell you all about it. :)


message
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


:

try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) +
Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


message
Hi

I have the following formula, which works when all fields for a date
range
are filled in. However, if there were no calls of a particular type
for
the
period, ACCESS returns a blank. How do I get ACCESS to
'recognise'
the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong
Number]+[Hang
Up
/
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
oho, way cool! added to both my IE and Netscape Favorites. big thanks,
Duane! :)


Duane Hookom said:
Check this out
http://home.bendbroadband.com/conradsystems/accessjunkie.html.

--
Duane Hookom
MS Access MVP


tina said:
Jeff has a newer site? hopefully he or somebody else will see this thread
and post the link! :)

btw, thanks for picking up on the table design issue, Duane - i should
have
addressed it, but was obviously asleep at the wheel when i answered this
one! <g>


Duane Hookom said:
You might also take a look at some information about normalization. Your
field names are "data values". Normalizing tables creates much more
flexibility in an application.

For instance, in a system like you have, what would you do if you
Click to expand...
wanted
to
track calls of "Heavy breathing"? I assume you would need to create a field
in a table and then modify forms, queries, expression (note the
Click to expand...
solution
to
this question), reports,....

Jeff Conrad has several links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.
Click to expand...
He
has
a newer site but I haven't favoritized it yet on this PC (sorry Jeff).

--
Duane Hookom
MS Access MVP


you're welcome, glad it worked for you. take a look at the Nz Function
topic
in Access Help, it'll tell you all about it. :)


message
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about
Click to expand...
[I'm
an
Access newbie]
--
Sue Compelling


:

try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) +
Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


message
Hi

I have the following formula, which works when all fields for a date
range
are filled in. However, if there were no calls of a particular type
for
the
period, ACCESS returns a blank. How do I get ACCESS to
'recognise'
the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong
Number]+[Hang
Up
/
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
ahhh, poor Lynn....don't feel too bad - all youngsters eventually must
strike out on their own. i'm sure he'll call often and visit from time to
time (at least when he needs to do laundry or eat a square meal). meanwhile,
go ahead and take advantage of all that extra space; turn his old room into
a study or game room! <g>
 
ahhh, poor Lynn....don't feel too bad - all youngsters eventually must
strike out on their own. i'm sure he'll call often and visit from time to
time (at least when he needs to do laundry or eat a square meal).
meanwhile,
go ahead and take advantage of all that extra space; turn his old room
into
a study or game room! <g>
Click to expand...

Ah, tina, but he's my first child to go out on his own... LOL. It may be too
much to take. Game room, huh? Not a bad idea. Jeff Who? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
Thanks Duane

I wondered what 'normalisation' was [saw it in a cvouple of threads] - and
knew my naming conventions were probably wrong - so this will be a good help -

Cheers
--
Sue Compelling


Duane Hookom said:
You might also take a look at some information about normalization. Your
field names are "data values". Normalizing tables creates much more
flexibility in an application.

For instance, in a system like you have, what would you do if you wanted to
track calls of "Heavy breathing"? I assume you would need to create a field
in a table and then modify forms, queries, expression (note the solution to
this question), reports,....

Jeff Conrad has several links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101. He has
a newer site but I haven't favoritized it yet on this PC (sorry Jeff).

--
Duane Hookom
MS Access MVP


tina said:
you're welcome, glad it worked for you. take a look at the Nz Function
topic
in Access Help, it'll tell you all about it. :)


message
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


:

try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for
the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang
Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
Thanks Nadeem .....
--
Sue Compelling


Nadeem said:
Sue Compelling

This is definition of NZ Function, This is not the proper way to tell
someone to search in help. This is the best way, I assume. This is Microsoft
way to exaplin thing in incomplete format.

Nadeem

Nz Function
You can use the Nz function to return zero, a zero-length string (" "), or
another specified value when a Variant is Null. For example, you can use this
function to convert a Null value to another value and prevent it from
propagating through an expression.

Nz(variant, [valueifnull])

=(Nz([211 Service Enquiries], 0)

In your case u are telling this function that return zero in case ([211
Service Enquiries] is null value.

U can also modify this function in case u want return some Text value when u
donot want to use this function in any formula like

=(Nz([211 Service Enquiries], " Your Value is Null")

Thanks

Sue Compelling said:
Brilliant Tina - that worked.

Tell me - what is the formula 'saying' - ie what the Nz all about [I'm an
Access newbie]
--
Sue Compelling


tina said:
try

=(Nz([211 Service Enquiries], 0) + Nz([Reception Services], 0) + Nz([Wrong
Number], 0) + Nz([Hang Up / Hoax / Nuisance], 0))

hth


Hi

I have the following formula, which works when all fields for a date range
are filled in. However, if there were no calls of a particular type for
the
period, ACCESS returns a blank. How do I get ACCESS to 'recognise' the
NULL
value and add up the rest of the fileds?

=([211 Service Enquiries]+[Reception Services]+[Wrong Number]+[Hang Up /
Hoax / Nuisance])

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top