sum five fields

[fnwtech]

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...

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

[ajalwaysus]

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]

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]

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...

Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
 
If Percent = 30 Then Percent = 100
ElseIf Percent = 29 Then Percent = 97
ElseIf Percent = 28 Then Percent = 94
ElseIf Percent = 27 Then Percent = 91
ElseIf Percent = 26 Then Percent = 89
ElseIf Percent = 25 Then Percent = 87
ElseIf Percent = 24 Then Percent = 85
ElseIf Percent = 23 Then Percent = 83
ElseIf Percent = 22 Then Percent = 81
etc 
end if

The compile error says Else without if

[ajalwaysus]

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...

Percent = NZ([field1], 0) + NZ([field2], 0) '...etc

This way nulls are substituted for zeros and the addition should then always work.

-AJ

[ajalwaysus]

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]

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

Here is the whole piece...

Private Sub Percent_GotFocus()
Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
 
If Percent = 30 Then Percent = 100
ElseIf Percent = 29 Then Percent = 97
ElseIf Percent = 28 Then Percent = 94
ElseIf Percent = 27 Then Percent = 91
ElseIf Percent = 26 Then Percent = 89
ElseIf Percent = 25 Then Percent = 87
ElseIf Percent = 24 Then Percent = 85
ElseIf Percent = 23 Then Percent = 83
ElseIf Percent = 22 Then Percent = 81
ElseIf Percent = 21 Then Percent = 79
ElseIf Percent = 20 Then Percent = 77
ElseIf Percent = 19 Then Percent = 74
ElseIf Percent = 18 Then Percent = 71
ElseIf Percent = 17 Then Percent = 69
ElseIf Percent = 16 Then Percent = 67
ElseIf Percent = 15 Then Percent = 65
ElseIf Percent = 14 Then Percent = 63
ElseIf Percent = 13 Then Percent = 61
ElseIf Percent = 12 Then Percent = 59
ElseIf Percent = 11 Then Percent = 57
ElseIf Percent = 10 Then Percent = 55
ElseIf Percent = 9 Then Percent = 53
ElseIf Percent = 8 Then Percent = 51
ElseIf Percent = 7 Then Percent = 49
ElseIf Percent = 6 Then Percent = 47
 
Else: present = 0
 
End If
 
 
End Sub

[ajalwaysus]

Try This,

Private Sub Percent_GotFocus()
Percent = [Understanding] + [Quality] + [Communication] + [Completion] + [Preparation] + [Participation]
 
If Percent = 30 Then
    Percent = 100
ElseIf Percent = 29 Then
    Percent = 97
ElseIf Percent = 28 Then
    Percent = 94
ElseIf Percent = 27 Then
    Percent = 91
ElseIf Percent = 26 Then
    Percent = 89
ElseIf Percent = 25 Then
    Percent = 87
ElseIf Percent = 24 Then
    Percent = 85
ElseIf Percent = 23 Then
    Percent = 83
ElseIf Percent = 22 Then
    Percent = 81
ElseIf Percent = 21 Then
    Percent = 79
ElseIf Percent = 20 Then
    Percent = 77
ElseIf Percent = 19 Then
    Percent = 74
ElseIf Percent = 18 Then
    Percent = 71
ElseIf Percent = 17 Then
    Percent = 69
ElseIf Percent = 16 Then
    Percent = 67
ElseIf Percent = 15 Then
    Percent = 65
ElseIf Percent = 14 Then
    Percent = 63
ElseIf Percent = 13 Then
    Percent = 61
ElseIf Percent = 12 Then
    Percent = 59
ElseIf Percent = 11 Then
    Percent = 57
ElseIf Percent = 10 Then
    Percent = 55
ElseIf Percent = 9 Then
    Percent = 53
ElseIf Percent = 8 Then
    Percent = 51
ElseIf Percent = 7 Then
    Percent = 49
ElseIf Percent = 6 Then
    Percent = 47
 
Else: Percent = 0
End If
 
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]

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]

Try the following :

Private Sub Percent_GotFocus()
  Percent = [Understanding] + [Quality] + [Communication] + _
            [Completion] + [Preparation] + [Participation]
 
  Select Case Percent
  Case 27 To 30
    Percent = 100 - ((30 - Percent) * 3)
  Case 20 To 26
    Percent = 97 - ((30 - Percent) * 2)
  Case 19
    Percent = 74
  Case 6 To 18
    Percent = 95 - ((30 - Percent) * 2)
  Case Else
    Percent = 0
  End Select
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]

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]

@ajalwaysus
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

[ajalwaysus]

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. =)

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

Private Sub Percent_GotFocus()
  Percent = [Understanding] + [Quality] + [Communication] + _
            [Completion] + [Preparation] + [Participation]
 
  Percent = NZ(DLookup("CalcPercent", "Percent_Comp_Table", "OrigPercent = " & Percent),0)
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]

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

Thanks for the assistance.