Matrix (1 Viewer)

hazell · Sep 16, 2014

Hi,
I have two fields in my form, one called Offending Likelihood, in which I have a list from which the inputter can choose numbers 1 to 5 and a field called Offending Likelihood from which the inputter can choose the letters A to E. I have an another box called Offending Score in which I would like to display one of five risk assessment categories from 'Very Low' to 'Very High'. any ideas how I do this?
thanks
Hazell

Uncle Gizmo · Sep 16, 2014

hazell said:
I have a list from which the inputter can choose numbers 1 to 5

Do you mean a Combobox?

hazell · Sep 16, 2014

I used the look up wizard to write in the values 1 to and a to e

plog · Sep 16, 2014

Yes a custom function in a module. You've not stated it directly but I assume the numbers and the letters go into calculating this score. You would pass the function those 2 values, then write logic to calculate the score and return whatever value you want and display that in the Score control.

Uncle Gizmo · Sep 16, 2014

hazell said:
I used the look up wizard to write in the values 1 to and a to e

I'm not with you?

Do you display the numbers, and letters in a combobox? (Some call it a "Dropdown Box")

hazell · Sep 16, 2014

Yes, a drop down box, sorry.

hazell · Sep 16, 2014

this is what I have.
=IIf([Offending Consequence] = 1 And [Offending Likelihood]=[A], “Very Low”, False)

What have I done wrong? The error just says Check constraint in the dialog box and name? in the field on the form. cheers for your help

plog · Sep 16, 2014

The statement you posted, you treated the value 'A', like a field in your data. When you want to reference a field you bracket the name of it [YourFieldNameHere], when you want to use a text value ('This is a Text Value') you put quotes around it.

However, this implementation is the wrong directon to go. Let's do some math:

Numeric Values: 5
Letter Values: 5
Letter/Numeric Permutations: 25 (1A, 1B..2A, 2B...5D, 5F)

Are you really going to write 25 If statements to catch every permutation of letters and numbers? What happens when you decide to go to 6 letters and 7 numbers?

hazell · Sep 16, 2014

You are right about the 25 if statements of course, and the problem is that there are five other indicators in the risk assessment matrix too, such as Anit social behaviour or mental health. It works well on paper as we use a grid to arrive at a score, but on the database this makes for 125 different if statements although every one is scored 1 to 5 and a to b. If you can think of a more elegant way to do this I would be grateful

plog · Sep 16, 2014

I would create a table for it and then link to it in a query, or if you wanted to get them one at a time, do a Dlookup (http://www.techonthenet.com/access/functions/domain/dlookup.php).

Uncle Gizmo · Sep 16, 2014

I think you would be better off doing some sort of calculation based on the entries you could use one of the fields in the combo box that displays a letter to hold a number that could then be extracted and used in the calculation.

Matrix (1 Viewer)

hazell

Registered User.

Uncle Gizmo

Nifty Access Guy

hazell

Registered User.

plog

Banishment Pending

Uncle Gizmo

Nifty Access Guy

hazell

Registered User.

hazell

Registered User.

plog

Banishment Pending

hazell

Registered User.

plog

Banishment Pending

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread