How to validate sum of fields

[pimmc]

Hi,

I'm running a query in order to find records that have at least one match, based on a form - where I select a certain record - and a subform -where the matching records are displayed. The search is based on four parameters (located in the main form), which are scored from 0 to 1 (passing through 0,25, 0,5 and 0,75). The results that appear in the subform are ranked and filtered according to the sum of those four criteria and corresponding scores.

Now, my problem is very simple but so far I haven't found a solution that works: I want to validate the sum of those four criteria scores so that it always equals 1; in case this condition isn't met, the query shouldn't be updated.

I've tried putting this piece of code in the (main/sub) form's properties (namely, in the before/after update event) but it isn't working:

If Score1 + Score2 + Score3 + Score4 <> 1 Then
MsgBox "Verify sum"
Cancel = True
Me.Undo
End If

Thanks in advance,

Paula

 

[DCrake]

If Nz(me.Score1) + Nz(me.Score2) + Nz(me.Score3) + Nz(me.Score4) > 1 Then

You had it set to Not Equals to 1 instead of greater than 1

This syntax is based on the controls names being a described above.

You may need to wrap the Nz() around each control to cope with missing values.

 

[pimmc]

Re: How to validate sum of fields (access 2003)

I'm sorry, I meant "so that the sum always equals 1" (already corrected in the original message). Anyway, I tried your solution (put the code in the subform's before update event), but it's still not working... Please, check the enclosed file...

Thanks

 

[DCrake]

You are trying to perform a calulation in a subform where the controls are in the main form. You need to move the calulation to the main form.

 

[pimmc]

Ok, I've done that , but the error continues to be ignored. The code works (without stopping the query updating however) when I put it in the after update event of a field (for instance, "Id_Of", or "ScoreMin"), but not on the form's properties, where I need it...

 

[pimmc]

Hello?
I still need help with this
Thanks

Ok, I've done that , but the error continues to be ignored. The code works (without stopping the query updating however) when I put it in the after update event of a field (for instance, "Id_Of", or "ScoreMin"), but not on the form's properties, where I need it...