Re: Sum several fields and "IF"

Tech-Archive recommends: Fix windows errors by optimizing your registry

SJW,
Aren't these Criteria fields on a form already? The table that holds
these Criteria fields should be in the RecordSource for your form... either
as a table, or a query.
If you place a text control on the form with a ControlSource of
[Criteria1], does it display or accept a value without a #Name error? How
about [Criteria2]... etc.. etc...

When all 12 values are on the form, and have been given values, then a
Text Control with a calculated ControlSource of
=NZ([Criteria1]) + Nz([Criteria2]) + etc........ + NZ([Criteria12])
should yield the [Score].

You should not have a [Score] field in your table! Score is a calculated
field only, and is not saved. Since you have Criteria 1-12 values stored,
you can always recalculate [Score] in any subsequent form, query, or report.

If you are unfamiliar with how to use control events to run VB code in
the form module, then try this instead...
Create another "unbound" calculated field with this for a
ControlSource... (all one line)
=IIF(Score<20,"Proceed", IIF(Score=>20 and Score<50, "Proceed with
Caution",IIF(Score=>50 and Score<75,"Serious Doubts", "Don't Proceed")))

This method should work (I couldn't test), but it is a bit cumbersome.
The best way is to use the OnClick event of a button (say... Evaluate), that
would run an Event Procedure that utilizes a Select Case to make the Score
evaluation. Check Help on Event Procedures and Select Case.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" <SJW123@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B2943C47-8135-48A2-A75D-3DEAC2780740@xxxxxxxxxxxxxxxx
> Thanks Al.
> Can you pls provide more details. I have a form based on tblProject and a
> field from tblProject called Score. The 12 criteria fields are in
> tblProject.
> I presume a calc txt control is a text box? Therefore, at the control
> source
> of the txtbox, which I have named 'score' I have
> =Nz([tblProject]![Criteria1]) + Nz([tblProject]![Criteria2])
> .....................Nz([tblProject]![Criteria12]) . But this is not
> correct
> as the #Name? error appears in the Score txt box. Or should I be using a
> field named 'score' on tblProject?
> Grateful any further advice broken down for a beginner.
> many thanks
> sjw
>
> "Al Camp" wrote:
>
>> SJW,
>> To sum all the fields, create a calculated text control called
>> TotalScore
>> with a ControlSource of... (use your own names)
>> =NZ([F1]) + NZ([F2]) + NZ([F3]).... etc... to NZ([F12])
>> Place a button on your form called cmdEvaluate, and on the OnClick
>> event,
>> use a SelectCase statement to evaluate the TotalScore value, and post the
>> appropriate textual message to the user.
>> --
>> hth
>> Al Camp
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>>
>> "SJW123" <SJW123@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:23E7A0B9-7133-48C3-BF80-A983CEDAE956@xxxxxxxxxxxxxxxx
>> > On my form I have 12 criteria (fields). Within each criteria the person
>> > must
>> > rank that specific criteria from 1 to 10 (best to worst). At the end of
>> > the
>> > 12 criteria, I would like to be able to provide a penultimate field
>> > which
>> > sums all 12 criteria values to provide a total score. Then, with this
>> > total
>> > score, I want a final result to be chosen from one of four
>> > possibilities
>> > ie.
>> > <20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts;
>> > and
>> > >75
>> > : don't proceed. The remainder of the database is working well, but it
>> > would
>> > be more effective if we could provide an automatic tallying and advice
>> > as
>> > above.
>> > I hope this is enough information to provide advice.
>> > many thanks
>> > Steven
>>
>>
>>


.

Quantcast