How do I add 5 seperate number entries into a single line total?

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

Guest

I have converted 5 separate field date entries into separate fields
converting them to a numeric value. I need to add each one to the other for a
total line entry. Each one looks for a date entry, then shows a number 1 for
it.
example: 1CapDateNbr: IIf([1Capdate]=Yes,"1"," ")
2CapDateNbr: IIf([2Capdate]=Yes,"1"," ")
3CapDateNbr: IIf([3Capdate]=Yes,"1"," ")
4CapDateNbr: IIf([4Capdate]=Yes,"1"," ")
5CapDateNbr: IIf([5Capdate]=Yes,"1"," ")
I need to add them together.
Thanks for your help.
 
Try returning a number, instead of a string.

5CapDateNbr: IIf([5Capdate]=Yes,1,0)

If 5Capdate is a boolean value then you could just add the five fields
together and use Abs to convert the negative value to a postive value
Abs(1CapDateNbr+2CapDateNbr+3CapDateNbr+4CapDateNbr+5CapDateNbr)

Based on the field names, I suspect that you really need to change your
table structure since this looks as if your data is not normalized.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John, I'll give that a try. I recently enherited this database and am
trying to make it work, but the more I work with it, the more I see the need
to make alot of sturcture changes. Thanks again

John Spencer said:
Try returning a number, instead of a string.

5CapDateNbr: IIf([5Capdate]=Yes,1,0)

If 5Capdate is a boolean value then you could just add the five fields
together and use Abs to convert the negative value to a postive value
Abs(1CapDateNbr+2CapDateNbr+3CapDateNbr+4CapDateNbr+5CapDateNbr)

Based on the field names, I suspect that you really need to change your
table structure since this looks as if your data is not normalized.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TEM said:
I have converted 5 separate field date entries into separate fields
converting them to a numeric value. I need to add each one to the other
for a
total line entry. Each one looks for a date entry, then shows a number 1
for
it.
example: 1CapDateNbr: IIf([1Capdate]=Yes,"1"," ")
2CapDateNbr: IIf([2Capdate]=Yes,"1"," ")
3CapDateNbr: IIf([3Capdate]=Yes,"1"," ")
4CapDateNbr: IIf([4Capdate]=Yes,"1"," ")
5CapDateNbr: IIf([5Capdate]=Yes,"1"," ")
I need to add them together.
Thanks for your help.
Click to expand...
Click to expand...
 
Back
Top