Calculating the values from different feilds & display on the form.

[siapliw]

Hi, further to my yesterday's question, need some more help.

I have 6 drop down combos in a form. User selects one value from a drop down combo in the form.


Each label has a value. e.g. >=7.7 has 5, 7.6-7.69 has 4, 7.5-7.59 has 3, etc. Values are saved in different tables. Corresponding value (like 4 or 2 or 0, etc) from the user selected label, will be used to calculate the scores. Scores from each combo as well as total scores from all the combos needs to be displayed & calculated on the form as well. What is the method to do this?
Thanks in advance.

[June7]

Use texbox ControlSource to display the combobox hidden value:
=[combo1]
=[combo2]
etc

You want to sum the 6 values? Expression in a textbox ControlSource:
=[combo1] + [combo2] + [combo3] + [combo4] + [combo5] + [combo6]

[siapliw]

Thanks for the reply.
But, Combo box displays ID. I need to display & calculate the related data from the other fields.
Table_pH- structure is like this:
pHvalID: ID, PK
pHScore:Number, for SCORES
pH: text

Table_Pat:
patID:ID, PK
pHvalID: FK to Table_pH.pHvalID
pHscore: need to get the data form the field Table_pH.pHScore
I need help here.
thanks.

[June7]

Why are you displaying ID in combobox? That means nothing to the user. Display the pHScore.

If value is not in bound column then reference its column by index number. Index begins with 0. So column 1 would have index of 0, column 2 has index of 1.

=combo1.Column(1)

[siapliw]

thank you very much June7 for your help.That works perfect, now.

I want to get the total of these values to be displayed in a text box.
If the value of [pHScore] is 2 & [pressureS] is 4,
=[tempS]+[pressureS] gives 24 not 6!

I tried DSum & Sum also. But, couldn't succeed.

[June7]

You want to sum records? Can't do that on unbound textboxes. Only fields can be summed.

Why do this on a form? Aggregate calcs are best done on report.

[siapliw]

Need to display the scores as per users' selection of the values from the combo boxes & get the total scores. Do I need to restructure my tables to do this?

[June7]

Maybe, I know nothing about your data structure.

Maybe you just need to build a report.

If you want to provide db for analysis, follow instructions at bottom of my post.

[siapliw]

hi June7, attaching the file to get an idea about the DB. I want to get the total scores in the 'Apache Scores' field. For my ease of understanding only two fields - Temperature & Mean Arterial Pressure - have been summed up.
thanks for offering help.

Database32.zip

[June7]

The expression =[tempS]+[pressureS] in apachescoreDisp textbox is not adding the values, it is concatenating. The + operator is a concatenation operator left over from ancient BASIC that VBA evolved from. Apparently, values in combobox columns are treated as strings. Use Val function to convert at least one of the values back to numbers: =Val([tempS])+[pressureS]. That will force Access to recognize that addition is desired operation as long as terms can be interpreted as numeric, meaning no alpha characters are encountered, otherwise expression will probably error.

[siapliw]

A big thanks.

[siapliw]

Another problem related to this is - How to avoid #Type! error. if the fields are null?
how can I use AvoidError=0?
Thanks!

[June7]

Use Nz function.

Val(Nz([tempS]))

[siapliw]

Thanks for the very quick reply June7.
that solves the problem of #Type! error. Then, until all the values from the combo box are selected, scores doesn't show up in the apache scores feild!

[June7]

Use the Nz function on each term: Val(Nz([tempS])) + Nz([pressureS])