Add up if then Statements

[Karl]

Hello,

I have five categories of membership. If a person registered for that
category an "X" is marked in the category. However, I know what these to be
numerical values associated with the cost of each category. So within my form
I made 5 additional fields with iif statements that basically read if the
field =x then its equal to 10 and if not its null (""). I then want to add up
these values to get a total cost spent by customer. I tried simply adding
them up but it seems that it just gives me each value instead a sum. So for
instance if category 1 =10, category 2 =10 and category 3 = 10. I should get
a sum of 30 instead its giving me 10 10 10. Is there away I can change this
to get a sum of these values??

Thanks.

 

[Allen Browne]

Karl, judging by your little parenthetical bit, I suspect you are using a
zero-length string - "" - rather than Null? If so, try replacing the "" with
the word Null.

Computers handle numbers differently than text. For example, you can add
numbers, e.g.:
1 + 1 yields: 2
or you can concatenate text together, e.g.:
"1" + "1" yields: 11

Since text is handled very differently than numbers, it's quite imporant to
make the distinction. When you use quotes, Access believes it's a text
operation, which explains why you get "10 10 10" instead of 30.

 

[Allen Browne]

Just to follow up, Karl, there's probably a better way to design this table.

Instead of 5 yes/no fields to handle the different categories, and then
write expressions to try to get the value of each one correct, it would make
more sense to create a table of categories and enter a record for each one
(its name, and the value.) You can then create a junction table between that
one and your main table. The junction table will have one *recod* for each
category that applies, instead of the 5 yes/no fields all in the one record.

If that's a new concept, is is absolutely crucial to understand. Here's some
more detail:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[Karl]

Thanks Allen worked perfectly!

Thanks for the additional information as well! Have a great day!

Karl