using if isnull

[Alvin]

I am trying to add values on a form to be used on a report but can't get it
to skip the fields that are null.
The following are the fields I need to add. Some have values and some don't
even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

 

[Baz]

=Sum(Nz([STA1])+Nz([STB1])+....

 

[Alvin]

STA1 has a vaue of 2
STB1 is null
STC1 has a value of 3
The following code returns "21" . I need it to add,
And thank you very much for the help
I am shortining the code for now here is what I have.
=Sum(Nz([STA1])+Nz([STB1])+Nz([STC1]))

=Sum(Nz([STA1])+Nz([STB1])+....

I am trying to add values on a form to be used on a report but can't get it
to skip the fields that are null.
The following are the fields I need to add. Some have values and some
don't
even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

 

[Stefan Hoffmann]

hi Alvin,

STA1 has a vaue of 2
STB1 is null
STC1 has a value of 3
The following code returns "21" . I need it to add,
And thank you very much for the help
I am shortining the code for now here is what I have.
=Sum(Nz([STA1])+Nz([STB1])+Nz([STC1]))

Use Nz([field],0).


mfG
--> stefan <--

 

[Baz]

It can't possibly return 21 based on the information you have given. Even
if they are Text fields it would return "23" not "21".

If they ARE text fields, you'll need to do this:

=Sum(Val(Nz([STA1,"0"]))+Val(Nz([STB1,"0"]))+Val(Nz([STC1,"0"])))

If they ARE text fields, why are you storing numbers as text, and then
performing arithmetic on them? Consider changing them to a numeric data
type.

Furthermore, although I don't know what they are for, having 14 fields with
near-identical names sets my alarm bells ringing. You might want to post
some details of your database design to get some views as to it's
suitability.

STA1 has a vaue of 2
STB1 is null
STC1 has a value of 3
The following code returns "21" . I need it to add,
And thank you very much for the help
I am shortining the code for now here is what I have.
=Sum(Nz([STA1])+Nz([STB1])+Nz([STC1]))

=Sum(Nz([STA1])+Nz([STB1])+....

I am trying to add values on a form to be used on a report but can't get
it
to skip the fields that are null.
The following are the fields I need to add. Some have values and some
don't
even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

 

[Rick Brandt]

I am trying to add values on a form to be used on a report but can't
get it to skip the fields that are null.
The following are the fields I need to add. Some have values and some
don't even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

I don't think you need the Sum() function for what you want. That is for
aggregating across multiple rows. If you just want the sum of your operand
fields then the "+" operator is already going to give you that. Just wrap all
of them in Nz() to handle the nulls.

 

[Alvin]

The details for the data base is as follows.
It is for keeping up with employees time on different jobs.
I have 7 different jobs and 14 employees per Forman.
Under each job on the form the Text boxes are for Straight Time and are set
to numbers "STA1 Thru STN1"
And "OTA1 Thru STN1" And "DTA1 Thru DTN1" For Double Time. The number "1" is
to Identify Job# 1. This will follow suit thrugh Job# 7.
On my forms it is ok for 0 to appear but not when I print the report to turn
in to our PayRoll dept.
Hope this helps

I am trying to add values on a form to be used on a report but can't
get it to skip the fields that are null.
The following are the fields I need to add. Some have values and some
don't even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

I don't think you need the Sum() function for what you want. That is for
aggregating across multiple rows. If you just want the sum of your operand
fields then the "+" operator is already going to give you that. Just wrap all
of them in Nz() to handle the nulls.

 

[Alvin]

I got it and thank you all so so much for the help. here is the code incase
some one needs the help
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+Val(Nz([STC1],0))+Val(Nz([STD1],0))+Val(Nz([STE1],0))+Val(Nz([STF1],0))+Val(Nz([STG1],0))+Val(Nz([STH1],0))+Val(Nz([STI1],0))+Val(Nz([STJ1],0))+Val(Nz([STK1],0))+Val(Nz([STL1],0))+Val(Nz([STM1],0))+Val(Nz([STN1],0))

Again, I can't thank you all enough that are willing to take time out to
help us all when we need it. You truely are VIP!

The details for the data base is as follows.
It is for keeping up with employees time on different jobs.
I have 7 different jobs and 14 employees per Forman.
Under each job on the form the Text boxes are for Straight Time and are set
to numbers "STA1 Thru STN1"
And "OTA1 Thru STN1" And "DTA1 Thru DTN1" For Double Time. The number "1" is
to Identify Job# 1. This will follow suit thrugh Job# 7.
On my forms it is ok for 0 to appear but not when I print the report to turn
in to our PayRoll dept.
Hope this helps

I am trying to add values on a form to be used on a report but can't
get it to skip the fields that are null.
The following are the fields I need to add. Some have values and some
don't even have a "0" and can't have a "0" in them.


=Sum([STA1]+[STB1]+[STC1]+[STD1]+[STE1]+[STF1]+[STG1]+[STH1]+[STI1]+[STJ1]+[STK1]+[STL1]+[STM1]+[STN1])


Thanks in advance.

I don't think you need the Sum() function for what you want. That is for
aggregating across multiple rows. If you just want the sum of your operand
fields then the "+" operator is already going to give you that. Just wrap all
of them in Nz() to handle the nulls.

 

[Stefan Hoffmann]

hi Alvin,

It is for keeping up with employees time on different jobs.
I have 7 different jobs and 14 employees per Forman.
Under each job on the form the Text boxes are for Straight Time and are set
to numbers "STA1 Thru STN1"
And "OTA1 Thru STN1" And "DTA1 Thru DTN1" For Double Time. The number "1" is
to Identify Job# 1. This will follow suit thrugh Job# 7.
On my forms it is ok for 0 to appear but not when I print the report to turn
in to our PayRoll dept.

Seems that your table design needs some normalization. Your tables
should look like that:

Table EMPLOYEE:
- ID, AutoInc, primary key
- EmplName, Text, not null, no empty string
- ..

Table JOB:
- ID, AutoInc, primary key
- JobName, Text, not null, no empty string
- ..

Table TIME:
- ID, AutoInc
- Employee_ID, Long Integer, not null, references EMPLOYEE.ID
- Job_ID, Long Integer, not null, references JOB.ID
- IsDoubleTime, Yes/No
- Time, DateTime, not null
The primary key is (Employee_ID, Job_ID, IsDoubleTime).



mfG
--> stefan <--