How do I display a description in a combobox, but have a calculation attached to the same row for a different column?

Rank.accdbI'm setting up a calculation in which the users select a dropdown option from 8 different combo boxes.
Each option is assigned a value that is multiplied by a factor, depending on which combo box it came from.
For example,
cboBox may have a factor of 5.
option B in that cboBox  may have a value of 3.
Result, 3 x 5 = 15 for cboBox.

Each of the combo boxes will work the same way. Then, those results are added together for the final ranking to go into the textbox, Rank.

I've tried both of the calculations below in the Rank box on the Frm_ISSUES (attached), neither works, and I don't know what to try next.

Me.Rank = 7 * Me.UrgLvlWt + 5.5 * Me.VolLvlWt + 5 * Me.TrnLvlWt + 4.5 * Me.LocLvlWt + 4 * Me.PerLvlWt + 2 * Me.QtyLvlWt + 1.25 * Me.WkldLvlWt + 1 * Me.PrdLvlWt

Me.Rank = 7 * Me.cboUrgency.Column(2) + 5.5 * Me.cboVolume.Column(2) + 5 * Me.cboTurnaround.Column(2) + 4.5 * Me.cboUsersLocation.Column(2) + 4 * Me.cboUsersPercentage.Column(2) + 2 * Me.cboQuality.Column(2) + 1.25 * Me.cboWorkloadMgmt.Column(2) + 1 * Me.cboProduction.Column(2)

The calculation below does work on frmRank, but it is only numbers on that form. It doesn't display line item descriptions and reference a column related to the description.

    Me.Rank = 7 * Me.Urgency + 5.5 * Me.Volume + 5 * Me.Turnaround + 4.5 * Me.UsersLocation + 4 * Me.UsersPercentage + 2 * Me.Quality + 1.25 * Me.WorkloadMgmt + 1 * Me.Production

I haven't set up the rank text box to requery, yet. So the code is run on click in the Rank text box.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You have empty values in your tables. Empty is not equal to 0. Fill all fields with numbers or add something like to:
If Me.cboUsersPercentage.Column(2) <> "" Then Me.txtRank = Me.txtRank + 4 * Me.cboUsersPercentage.Column(2)
PatHartmanCommented:
Your expression includes both + and *.  You need parentheses in order to coerce Access into evaluating the expression as you want it evaluated.  Right now, Access is using Boolean logic so:
Me.Rank = 7 * Me.UrgLvlWt + 5.5 * Me.VolLvlWt + 5 * Me.TrnLvlWt + 4.5 * Me.LocLvlWt + 4 * Me.PerLvlWt + 2 * Me.QtyLvlWt + 1.25 * Me.WkldLvlWt + 1 * Me.PrdLvlWt
=
Me.Rank = (7 * Me.UrgLvlWt) + (5.5 * Me.VolLvlWt) + (5 * Me.TrnLvlWt) + (4.5 * Me.LocLvlWt) + (4 * Me.PerLvlWt) + (2 * Me.QtyLvlWt) + (1.25 * Me.WkldLvlWt) + (1 * Me.PrdLvlWt)

This is probably correct based on your description but best practice would be to use parentheses to make the evaluation explicit.  The problem is that if any of the controls are null, the expression will return null.

Me.Rank = (7 * Nz(Me.UrgLvlWt,0)) + (5.5 * Nz(Me.VolLvlWt,0)) + (5 * Nz(Me.TrnLvlWt,0)) + (4.5 * Nz(Me.LocLvlWt,0)) + (4 * Nz(Me.PerLvlWt,0)) + (2 * Nz(Me.QtyLvlWt,0)) + (1.25 * Nz(Me.WkldLvlWt,0)) + (1 * Nz(Me.PrdLvlWt,0))

We need more than "it doesn't work" to go further.
Dale FyeOwner, Developing Solutions LLCCommented:
OK, you can use the ControlSource of your Rank textbox, something like:

ControlSource: = 7 * NZ(Forms!YourFormName.cbo_Urgency, 0) + 5.5 * NZ(Forms!YourFormName.cbo_Volume, 0) + 5 * NZ(Forms!YourFormName.cbo_Turnaround, 0) + 4.5 * NZ(Forms!YourFormName.cbo_UsersLocation, 0) + 4 * NZ(Forms!YourFormName.cbo_UsersPercentage, 0) + 2 * NZ(Forms!YourFormName.cbo_Quality, 0) + 1.25 * NZ(Forms!YourFormName.cbo_WorkloadMgmt, 0)+ 1 * NZ(Forms!YourFormName.cbo_Production, 0)

This would code refers to the bound column in each of the combo boxes, not to the underlying field.  It also uses the NZ() function to convert NULL values to zeros.  Without this feature, your txt_Rank will be blank until every one of the combo boxes has data entered into it.

If you need to refer to another column in each of the combo boxes rowsource, you would use the Column( ) property of the combo box.  This property is zero based, so the 2nd column of cbo_WorkloadMgmt would be referred to as:

Forms!YourFormName.cbo_WorkloadMgmt.column(1)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
Pat,

;-(
 
I know you know this, but you don't need parenthesis to force Access to evaluate that function accurately.  It will always evaluate the multiplication first and then evaluate the addition.

Dale    ;-)
David BigelowStaff Operations SpecialistAuthor Commented:
als315,

Each description in the combo box has a value attached to it in the row source table. Maybe part of my problem is that value is not passing to the rank table when I select the description. If so, how would I have that value passed to the table?IssuesDatabase.accdb
PatHartmanCommented:
Dale,
As I said, this expression happens to work as written but what about

a + b * c

Do you intend that to be (a+b) * c  OR a + (b*c)?
It makes a difference and way too many people don't understand that or get sloppy with writing the expression.
 
 10 + (3 * 4) = 22
(10 + 3) * 4 = 52
Getting used to expressly using parentheses always ensures the expression evaluates as you intend.

David,
If the bound column is the number, then the expression will work as written.  Are you asking how to show a text field and still calculate using the bound field?
In the Select for the combo's RowSource select the numeric field and the text field.  In the  column widths property use 0",2"  to hide the first field and show the second.
David BigelowStaff Operations SpecialistAuthor Commented:
I followed the PEMDAS logic where I expected the multiplication to happen before the addition.
PatHartmanCommented:
I was attempting to use this complex expression as a "teaching" example.  Yes, I know the expression works as written.  I have said that twice.  And now a third time.  Not everyone understands how expressions using different classes of operators are evaluated.  Think of always using parentheses in complex expressions as defensive programming.
Dale FyeOwner, Developing Solutions LLCCommented:
Pat,

I made my assumption based on the OP's original comment where he indicated that each combo box has  a factor and that factor is multiplied by the value associated with the combo selection.

"Think of always using parentheses in complex expressions as defensive programming. "

I would strongly agree with that!
PatHartmanCommented:
I knew we would eventually be on the same page on this issue:)
Dale FyeOwner, Developing Solutions LLCCommented:
David,

I'm on my iPad, so cannot look at your database.  But if the multiplier associated with each combo (not the value associated with each combos selection) is in the RowSource of each combo, you would do something like:

ControlSource: = (VAL(forms!YourFormName.cbo_Urgency.column(1)) * NZ(Forms!YourFormName.cbo_Urgency, 0))
 + (VAL(forms!YourFormName.cbo_Volume.column(1)) * NZ(Forms!YourFormName.cbo_Volume, 0))
 + (VAL(forms!YourFormName.cbo_TurnAround.column(1)) * NZ(Forms!YourFormName.cbo_Turnaround, 0))
 + (VAL(forms!YourFormName.cbo_UsersLocation.column(1)) * NZ(Forms!YourFormName.cbo_UsersLocation, 0))
+ (VAL(forms!YourFormName.cbo_UsersPercentage.column(1)) * NZ(Forms!YourFormName.cbo_UsersPercentage, 0) )
+ (VAL(forms!YourFormName.cbo_Quality.column(1)) * NZ(Forms!YourFormName.cbo_Quality, 0)
+ (VAL(forms!YourFormName.cbo_WorkloadMgmt.column(1))  * NZ(Forms!YourFormName.cbo_WorkloadMgmt, 0))
+ (VAL(forms!YourFormName.cbo_Production.column(1)) * NZ(Forms!YourFormName.cbo_Production, 0))

Notice that I've added the extra ( ) to group the rank value and the combo box selection evaluated together.  I've also used the Val( ) function to change the value of that combo box column to numeric.  Sometimes Access will do this on its own, others, not so much, so I tend to do it explicitly.
als315Commented:
David, let's return to your first example. There were unbounded fields with lookup to some tables. 2 of your tables has no data in referenced fields:
12If you fill empty fields with 0 or with any numbers, calculation will be done. Make this field "required" and you will have no problems until you will have no empty fields on your form. In this case you should have some logiс - is it possible or no. May be you should not allow calculation of rank if any of fields is empty.
Rank.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BigelowStaff Operations SpecialistAuthor Commented:
als315,

Thank you so much! Those empty fields were the culprit from the get go. You mentioned it up front, but I didn't quite follow it through in my thinking. I think that is because I was working with a couple of samples that had different settings. But, with that said, I can't believe I was actually so close to the final answer with my "combo box and Column(2)" equation! I think I would have gotten it right away had I remembered to put values in those tables and tightened up a few other settings. Next time, I think I'll write down how each table is formatted and then checkmark each one as I go. It was tiresome trying to remember or to change each setting for eight tables. Or, maybe I'll isolate two tables and work on getting a calculation that works for two instead of troubleshooting eight of them.

Спасибо за вашу помощь!

Pat,

I used parenthesis and added the Nz coding to als315's answer. That Nz is nice, because the main user wanted to see the ranking progressively as she fills in each factor. I'll add the requeries.

Dale,

I gave your formula a try, but if the level weight values were not in two of the tables of the sample I was working with, that may have been why it didn't work. After that, I didn't want to keep testing in two different methods, VBA and control source, so I stuck with VBA.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.