2 calculations based on 1 dropdown answer but using different rows from the dropdown

[CraigR]

I have a dropdown combo box that includes four fields and I would like to run two calculations

I could have a dropdown1 with Membrane or No Membrane to act as a filter


then dropdown2 with Climatic Zone A,B or C
and then do the calculation

However I need to give the calculation for BOTH options.

The combo box is based on the following info
if "no" membrane "With" membrane class
Climatic Zone A 2000 1000 Climatzone
Climatic Zone B 4000 2000 Climatzone
Climatic Zone C 6000 3000 Climatzone

Field1 dropdown combo = Climatic Zone A,B or C
in the data section of the property sheet, in Row Source
I have 4 rows/fields based in the info above

Field2 is a number field that I input an area

What I am trying to do is calculate Field1 (row2) x Field2 = answer while at the same time calculating Field1 (row3) x Field2
so lets say I selected Climatic Zone A in Field1 dropdown and in Field2 I added 380.

I want the calculation 2000 x 380 = 760,000 | 1000 x 380 = 380,000

If I were doing this in excel I could use =VLOOKUP to pull the info out of each cell to run the calc's


with thanks
Craig

[June7]

I cannot reconcile references to row2 and row3 with the numbers used in the calculation. Both numbers appear to be from row1. So I am confused.

[CraigR]

Sorry, I haven't explained it all that well.

The Dropdown = SELECT GeneralInfo.Item, GeneralInfo.Number0, GeneralInfo.Number1, GeneralInfo.Number2, GeneralInfo.Number3, GeneralInfo.Class FROM GeneralInfo WHERE (((GeneralInfo.Class)="Climatic Zone"));

GeneralInfo.Item = Climatic Zone A or Climatic Zone B or Climatic Zone C
GeneralInfo.Number0 = 2000 or 4000 or 6000 *these numbers relate to If there is a membrane on the ground then these apply*
GeneralInfo.Number1 = 1000 or 2000 or 3000 *these numbers relate to If there is NO membrane on the ground then these apply*

I need to calculate both sets of numbers as an option to either "membrane or no membrane"

so if I select Climatic Zone A from the dropdown then it would calculate ([2000]*[anotherfield])=X | and ([1000]*[anotherfield]) = Y


Normally I would go
The Dropdown = SELECT GeneralInfo.Number0, GeneralInfo.Item, GeneralInfo.Class FROM GeneralInfo WHERE (((GeneralInfo.Class)="Climatic Zone"));
and set columns to 0;2. So that the number would be selected but the text would be shown in the dropdown, however this would only allow for 1 calculation.

With thanksCraig

[June7]

Your combobox RowSource does not include Number1 field, however, since it is exactly half of Number0 just divide by 2 - unless that is not always the case. Include Number1 in the RowSource query - displayed or hidden. Reference combobox columns by index. Index begins with 0. If Number2 is in column 2 the index is 1: ComboboxName.Column(1)

[ArviLaanemets]

At start, your GeneralInfo table is not normalized, and as result, it will be difficult get normal combo - you have to use UNION query with 3 instances of your GeneralInfo table as source currently.

Or simply redesign the table:
GeneralInfio: ID, Item, Class, Type, Value, where in field Type you store a value indicating for class 'Climatic Zone', is there membrane on ground or not (p.e. TRUE/FALSE). ID can be autonumeric primary key, as you'll have several Items with same value in table for now and Item can't serve as PK anymore.

Then your combo will be based on query
SELECT DISTINCT Value, Item & Iif(Type, " with membrane on ground", " without membrane on ground") & " (" & CStr(Value) & ")" AS [SelectionInfo] FROM GeneralInfo WHERE (((GeneralInfo.Class)="Climatic Zone"))

Set combos BoundColumn property =1, ColumnCount property to 2, and ColumnWidths property to '0,2.5' (the second number can be any positive number - the setting estimates, that the user sees SelectionInfo column from query in combo box - you may have more columns in query, but the only the first one with width > 0 is displayed.).

The value for combo box will be Value from table GeneralInfo according your selection.

[ssanfu]

Note that "Class", "Type" and "Value" are reserved words and shouldn't be used for object names.

[ArviLaanemets]

Note that "Class", "Type" and "Value" are reserved words and shouldn't be used for object names.

Sorry for this - I was almost sleeping when I posted this.

[ssanfu]

In the example dB, there are three methods of doing the calculation: using a subroutine, using a function or doing the calculation in the control.

[CraigR]

Hi Steve

Mate THANK YOU!

Craig

[ssanfu]

Good luck with your project....