Calculated field on a form not working correctly

[danielrogers1]

I have attached the database I'm working on (it has been several months without messing with it due to other obligations and priorities at work). The primary issue I'm having is that on the frmMonitorEvent the second combo box (sngQ2Points) if it is left as N/A works just fine. But if any of the other options are selected, instead of adding all of the cells into the Points Earned box, it starts to just add the numbers at the end (i.e. instead of 3+3+20 = 26 it will come up with 332) Any ideas what's causing this?

[June7]

The + character has two special purposes. One is for addition and the other is concatenation. The concatenation functionality is holdover from ancient BASIC program. The fields the comboboxes are bound to are text type. Therefore, the data is text, even if only digits, and will be concatenated. Your options are:

1. change the fields to number type, Double to allow for decimals - and don't use N/A value
=Nz([Combo309],0)+Nz([sngq2points],0)+Nz([combo317],0)+Nz([combo319],0)+Nz([combo321],0)+Nz([combo323],0)+Nz([combo325],0)+Nz([combo327],0)+Nz([combo329],0)+Nz([combo331],0)+Nz([combo333],0)+Nz([combo335],0)+Nz([combo337],0)+Nz([combo417],0)+Nz([combo419],0)+Nz([combo421],0)+Nz([combo423],0)+Nz([combo605],0)+Nz([combo607],0)+Nz([combo609],0)+Nz([combo611],0)+Nz([combo613],0)+Nz([combo615],0)+Nz([combo617],0)

2. more complicated expression to sum the values

This is not a normalized data structure. Survey questionnaire db has been topic of numerous threads. Try searching forum.

[danielrogers1]

I know it's not normalized, but it's what my boss wants so I'm sort of left to figure out some way to try to make it work. All of the other combo boxes will correctly sum, it is only if the second one is selected that it concatenates, I was hoping there would be an easy way around it, but it doesn't appear so. Thank you for your help!

[June7]

That is odd. Sorry, should have read OP more carefully. Blows a hole in my conclusion.

So I did some more testing with your original setup. I selected value in combobox 2 first then selected other comboboxes and the calc works.

I selected combobox 20 first then combobox 1 then combobox 2 - calc fails.

Selecting value in comboboxes 2 through 24 works. Then selecting 0, 1.5, or 3 in combobox 1 fails.

Selecting value in all comboboxes except 2 works. Then selecting 0, 1.5, or 3 in combobox 2 fails.

Something about comboboxes 1 and 2 together.

[danielrogers1]

Yea, it seems one of the first two must be set to N/A and everything else will calculate correctly, but it concatenates if a number is selected in both. Curiouser and curiouser.

[June7]

The ideal solution would be normalized data structure.

Next to ideal is at least have the field set to number type.

However, this seemed to resolve the issue:
=IIf([combo309]="N/A",0,Val([Combo309]))+IIf([Combo308]="N/A",0,[Combo308])+IIf([Combo317]="N/A",0,[Combo317])+IIf([Combo319]="N/A",0,[Combo319])+IIf([Combo321]="N/A",0,[Combo321])+IIf([Combo323]="N/A",0,[Combo323])+IIf([Combo325]="N/A",0,[Combo325])+IIf([Combo327]="N/A",0,[Combo327])+IIf([Combo329]="N/A",0,[Combo329])+IIf([Combo331]="N/A",0,[Combo331])+IIf([Combo333]="N/A",0,[Combo333])+IIf([Combo335]="N/A",0,[Combo335])+IIf([Combo337]="N/A",0,[Combo337])+IIf([Combo417]="N/A",0,[Combo417])+IIf([Combo419]="N/A",0,[Combo419])+IIf([Combo421]="N/A",0,[Combo421])+IIf([Combo423]="N/A",0,[Combo423])+IIf([Combo605]="N/A",0,[Combo605])+IIf([Combo607]="N/A",0,[Combo607])+IIf([Combo609]="N/A",0,[Combo609])+IIf([Combo611]="N/A",0,[Combo611])+IIf([Combo613]="N/A",0,[Combo613])+IIf([Combo615]="N/A",0,[Combo615])+IIf([Combo617]="N/A",0,[Combo617])

Making sure one value was always a true number forces sum instead of concatenation.

In the Earned textbox at bottom: =[PointsEarned]