Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

sum five fields

fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#1: Oct 15 '09
I have two tasks for the same field. First, I need a field on a form to sum 5 other fields on the form...

The field is named percent. The five fields I want to sum are understanding, quality, communication, completion, preparation, participation.

I keep getting a compilation error. I am trying this on gotfocus. Can someone tell me how I would do this?

The second step is to take the sum and use if else then to convert the sum to a number grade...
Expand|Select|Wrap|Line Numbers
  1. If percent = 30 Then percent =100 elseif percent>=29 then percent=97
this part I have done before, so I think I am set here. I just can't seem to get the sum function to work.
Thanks!
smg
best answer - posted by ajalwaysus
Try This,
Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2. Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
  3.  
  4. If Percent = 30 Then
  5.     Percent = 100
  6. ElseIf Percent = 29 Then
  7.     Percent = 97
  8. ElseIf Percent = 28 Then
  9.     Percent = 94
  10. ElseIf Percent = 27 Then
  11.     Percent = 91
  12. ElseIf Percent = 26 Then
  13.     Percent = 89
  14. ElseIf Percent = 25 Then
  15.     Percent = 87
  16. ElseIf Percent = 24 Then
  17.     Percent = 85
  18. ElseIf Percent = 23 Then
  19.     Percent = 83
  20. ElseIf Percent = 22 Then
  21.     Percent = 81
  22. ElseIf Percent = 21 Then
  23.     Percent = 79
  24. ElseIf Percent = 20 Then
  25.     Percent = 77
  26. ElseIf Percent = 19 Then
  27.     Percent = 74
  28. ElseIf Percent = 18 Then
  29.     Percent = 71
  30. ElseIf Percent = 17 Then
  31.     Percent = 69
  32. ElseIf Percent = 16 Then
  33.     Percent = 67
  34. ElseIf Percent = 15 Then
  35.     Percent = 65
  36. ElseIf Percent = 14 Then
  37.     Percent = 63
  38. ElseIf Percent = 13 Then
  39.     Percent = 61
  40. ElseIf Percent = 12 Then
  41.     Percent = 59
  42. ElseIf Percent = 11 Then
  43.     Percent = 57
  44. ElseIf Percent = 10 Then
  45.     Percent = 55
  46. ElseIf Percent = 9 Then
  47.     Percent = 53
  48. ElseIf Percent = 8 Then
  49.     Percent = 51
  50. ElseIf Percent = 7 Then
  51.     Percent = 49
  52. ElseIf Percent = 6 Then
  53.     Percent = 47
  54.  
  55. Else: Percent = 0
  56. End If
  57.  
  58. End Sub
Your issue is that you have the assignment of a value after the "Then" on the same line, if I am right this only works if the entire "if then else" was on the same line together, which would be REALLY long and hard to follow.

Also, as a side note, some advice =) When you write this much hard code, you may want to consider storing this in a table or something that way your code won't be so long and won't be so tedious trying to change it if something changes.

Hope this helps,

-AJ



Expert
  
Join Date: Jul 2009
Location: KY
Posts: 253
#2: Oct 15 '09

re: sum five fields

You will need to post the code you are using to do this, for us to help you. It is hard to analyze your issue without seeing the code.

-AJ
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#3: Oct 15 '09

re: sum five fields

Okay, just got it. was thinking I needed to use sum, but because I was using different fields I was getting the error. switched it to percent=[field1]+[field2] etc works. I am set for now... I will see if I get stuck with the rest!
thanks !
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#4: Oct 15 '09

re: sum five fields

Okay, can I then use the else/elseif in the same set of code as the one above or should I create a field for the sum and then a new one for the else if statement? I tried adding it to the same and get an error...
Expand|Select|Wrap|Line Numbers
  1. Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
  2.  
  3. If Percent = 30 Then Percent = 100
  4. ElseIf Percent = 29 Then Percent = 97
  5. ElseIf Percent = 28 Then Percent = 94
  6. ElseIf Percent = 27 Then Percent = 91
  7. ElseIf Percent = 26 Then Percent = 89
  8. ElseIf Percent = 25 Then Percent = 87
  9. ElseIf Percent = 24 Then Percent = 85
  10. ElseIf Percent = 23 Then Percent = 83
  11. ElseIf Percent = 22 Then Percent = 81
  12. etc 
  13. end if
The compile error says Else without if
Expert
  
Join Date: Jul 2009
Location: KY
Posts: 253
#5: Oct 15 '09

re: sum five fields

OK glad that works, but there is one issue I know of when using the addition sign, if any of the fields you are adding up is null, the entire result will be null, to avoid this I usually write these like this...
Expand|Select|Wrap|Line Numbers
  1. Percent = NZ([field1], 0) + NZ([field2], 0) '...etc
This way nulls are substituted for zeros and the addition should then always work.

-AJ
Expert
  
Join Date: Jul 2009
Location: KY
Posts: 253
#6: Oct 15 '09

re: sum five fields

Please show us the entire IF code, I can't assume what you have past the "etc", and please put your code in [code] tags.

-AJ
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#7: Oct 15 '09

re: sum five fields

First, thanks for the tip on the null feature. I will fix that.

Here is the whole piece...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2. Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
  3.  
  4. If Percent = 30 Then Percent = 100
  5. ElseIf Percent = 29 Then Percent = 97
  6. ElseIf Percent = 28 Then Percent = 94
  7. ElseIf Percent = 27 Then Percent = 91
  8. ElseIf Percent = 26 Then Percent = 89
  9. ElseIf Percent = 25 Then Percent = 87
  10. ElseIf Percent = 24 Then Percent = 85
  11. ElseIf Percent = 23 Then Percent = 83
  12. ElseIf Percent = 22 Then Percent = 81
  13. ElseIf Percent = 21 Then Percent = 79
  14. ElseIf Percent = 20 Then Percent = 77
  15. ElseIf Percent = 19 Then Percent = 74
  16. ElseIf Percent = 18 Then Percent = 71
  17. ElseIf Percent = 17 Then Percent = 69
  18. ElseIf Percent = 16 Then Percent = 67
  19. ElseIf Percent = 15 Then Percent = 65
  20. ElseIf Percent = 14 Then Percent = 63
  21. ElseIf Percent = 13 Then Percent = 61
  22. ElseIf Percent = 12 Then Percent = 59
  23. ElseIf Percent = 11 Then Percent = 57
  24. ElseIf Percent = 10 Then Percent = 55
  25. ElseIf Percent = 9 Then Percent = 53
  26. ElseIf Percent = 8 Then Percent = 51
  27. ElseIf Percent = 7 Then Percent = 49
  28. ElseIf Percent = 6 Then Percent = 47
  29.  
  30. Else: present = 0
  31.  
  32. End If
  33.  
  34.  
  35. End Sub
Expert
  
Join Date: Jul 2009
Location: KY
Posts: 253
#8: Oct 15 '09

re: sum five fields

Try This,
Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2. Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
  3.  
  4. If Percent = 30 Then
  5.     Percent = 100
  6. ElseIf Percent = 29 Then
  7.     Percent = 97
  8. ElseIf Percent = 28 Then
  9.     Percent = 94
  10. ElseIf Percent = 27 Then
  11.     Percent = 91
  12. ElseIf Percent = 26 Then
  13.     Percent = 89
  14. ElseIf Percent = 25 Then
  15.     Percent = 87
  16. ElseIf Percent = 24 Then
  17.     Percent = 85
  18. ElseIf Percent = 23 Then
  19.     Percent = 83
  20. ElseIf Percent = 22 Then
  21.     Percent = 81
  22. ElseIf Percent = 21 Then
  23.     Percent = 79
  24. ElseIf Percent = 20 Then
  25.     Percent = 77
  26. ElseIf Percent = 19 Then
  27.     Percent = 74
  28. ElseIf Percent = 18 Then
  29.     Percent = 71
  30. ElseIf Percent = 17 Then
  31.     Percent = 69
  32. ElseIf Percent = 16 Then
  33.     Percent = 67
  34. ElseIf Percent = 15 Then
  35.     Percent = 65
  36. ElseIf Percent = 14 Then
  37.     Percent = 63
  38. ElseIf Percent = 13 Then
  39.     Percent = 61
  40. ElseIf Percent = 12 Then
  41.     Percent = 59
  42. ElseIf Percent = 11 Then
  43.     Percent = 57
  44. ElseIf Percent = 10 Then
  45.     Percent = 55
  46. ElseIf Percent = 9 Then
  47.     Percent = 53
  48. ElseIf Percent = 8 Then
  49.     Percent = 51
  50. ElseIf Percent = 7 Then
  51.     Percent = 49
  52. ElseIf Percent = 6 Then
  53.     Percent = 47
  54.  
  55. Else: Percent = 0
  56. End If
  57.  
  58. End Sub
Your issue is that you have the assignment of a value after the "Then" on the same line, if I am right this only works if the entire "if then else" was on the same line together, which would be REALLY long and hard to follow.

Also, as a side note, some advice =) When you write this much hard code, you may want to consider storing this in a table or something that way your code won't be so long and won't be so tedious trying to change it if something changes.

Hope this helps,

-AJ
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#9: Oct 15 '09

re: sum five fields

Thanks! that worked. I really need to rebuild the whole database - this was built by someone with even less knowledge then me ;-) and I am just trying to make it work for another year until we have time to tackle it.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#10: Oct 15 '09

re: sum five fields

Try the following :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2.   Percent = [Understanding] + [Quality] + [Communication] + _
  3.             [Completion] + [Preparation] + [Participation]
  4.  
  5.   Select Case Percent
  6.   Case 27 To 30
  7.     Percent = 100 - ((30 - Percent) * 3)
  8.   Case 20 To 26
  9.     Percent = 97 - ((30 - Percent) * 2)
  10.   Case 19
  11.     Percent = 74
  12.   Case 6 To 18
  13.     Percent = 95 - ((30 - Percent) * 2)
  14.   Case Else
  15.     Percent = 0
  16.   End Select
  17. End Sub
Failing a method such as AJ suggests (holding the values in a table), the Select Case construct is better for any complicated sets of comparisons.

I'm deeply curious as to why the strange set of values by the way? Why some gaps of 3 but mostly of 2?
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#11: Oct 15 '09

re: sum five fields

By the way, as a full member now, you should know that we expect your code to be posted in [code] Tags as AJ commented earlier (See How to Ask a Question and indeed the instructions in the posting window itself).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

Administrator.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#12: Oct 16 '09

re: sum five fields

Quote:

Originally Posted by ajalwaysus View Post

Try This,

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2. Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
  3.  
  4. If Percent = 30 Then
  5.     Percent = 100
  6. ElseIf Percent = 29 Then
  7.     Percent = 97
  8. ElseIf Percent = 28 Then
  9.     Percent = 94
  10. ElseIf Percent = 27 Then
  11.     Percent = 91
  12. ElseIf Percent = 26 Then
  13.     Percent = 89
  14. ElseIf Percent = 25 Then
  15.     Percent = 87
  16. ElseIf Percent = 24 Then
  17.     Percent = 85
  18. ElseIf Percent = 23 Then
  19.     Percent = 83
  20. ElseIf Percent = 22 Then
  21.     Percent = 81
  22. ElseIf Percent = 21 Then
  23.     Percent = 79
  24. ElseIf Percent = 20 Then
  25.     Percent = 77
  26. ElseIf Percent = 19 Then
  27.     Percent = 74
  28. ElseIf Percent = 18 Then
  29.     Percent = 71
  30. ElseIf Percent = 17 Then
  31.     Percent = 69
  32. ElseIf Percent = 16 Then
  33.     Percent = 67
  34. ElseIf Percent = 15 Then
  35.     Percent = 65
  36. ElseIf Percent = 14 Then
  37.     Percent = 63
  38. ElseIf Percent = 13 Then
  39.     Percent = 61
  40. ElseIf Percent = 12 Then
  41.     Percent = 59
  42. ElseIf Percent = 11 Then
  43.     Percent = 57
  44. ElseIf Percent = 10 Then
  45.     Percent = 55
  46. ElseIf Percent = 9 Then
  47.     Percent = 53
  48. ElseIf Percent = 8 Then
  49.     Percent = 51
  50. ElseIf Percent = 7 Then
  51.     Percent = 49
  52. ElseIf Percent = 6 Then
  53.     Percent = 47
  54.  
  55. Else: Percent = 0
  56. End If
  57.  
  58. End Sub
Your issue is that you have the assignment of a value after the "Then" on the same line, if I am right this only works if the entire "if then else" was on the same line together, which would be REALLY long and hard to follow.

Also, as a side note, some advice =) When you write this much hard code, you may want to consider storing this in a table or something that way your code won't be so long and won't be so tedious trying to change it if something changes.

Hope this helps,

-AJ

After thinking about what you wrote last night, I was curious... if I store the in a table, how would I "call" it into the form? The way this particular database was constructed, they just print off the form instead of using a report. Or is this just more work than it is worth at this point?!
Thanks
Expert
  
Join Date: Jul 2009
Location: KY
Posts: 253
#13: Oct 16 '09

re: sum five fields

If this is the only place you are writing this, then hard coding would be acceptable, the purpose of storing it in a table is so that you don't have to repeat your code across too many places.
If this code works then I would stick with it, but I would suggest you try NeoPa's code as well, because it is always nice to be able to trim down your code when possible. =)

Quote:

Originally Posted by fnwtech View Post

After thinking about what you wrote last night, I was curious... if I store the in a table, how would I "call" it into the form?

To answer your question though, if you ever wanted to do this, a simple way would be to write code like this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percent_GotFocus()
  2.   Percent = [Understanding] + [Quality] + [Communication] + _
  3.             [Completion] + [Preparation] + [Participation]
  4.  
  5.   Percent = NZ(DLookup("CalcPercent", "Percent_Comp_Table", "OrigPercent = " & Percent),0)
  6. End Sub
If you had a table named "Percent_Comp_Table" with 2 fields, named "CalcPercent" and "OrigPercent". You would lookup "OrigPercent" based on what you code returns for Percent, and the lookup would then return "CalcPercent" which is the value you wish to assign to Percent.

Let me know if you have any more questions or if this doesn't make sense.

-AJ
fnwtech's Avatar
Member
  
Join Date: Oct 2007
Posts: 37
#14: Oct 16 '09

re: sum five fields

I may give the table a try as I don't know the select case coding at all.

Thanks for the assistance.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,414 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved