Unanswered: Calculation using query created from multi-selection list box
I am creating a calculator to provide % figures based on number of items in a query generated by multi-selection list box [beginner using Access 97 - a bit behind the times!]
The query will retrieve data from two columns of its associated table where col 1 has the item name and col 2 its numerical value to 3 decimal places e.g.
Col 1 : Col 2
From the list box selections which provide the criteria for the query are then generates multiple records I want to calculate a cumulative frequency value for all the col 2 values e.g. rec 1 * rec 2 * rec * ... rec n; for example 4 records might be generated where frequency values are 0.200 * 0.112 * 0.210 * 0.701 = 0.003
In addition I would like to be able to keep the value which will be shown in a text box to 3 decimal places. Consequently if the answer is very small i.e. less than 0.001 e.g. 0.0000005 I would like the result to simply be shown as < 0.001 if this is possible by some conditional formatting or somehow
At the end, you could do a simple If statement along with the Format function (for large values) and a hardcoded result (for small values as you've defined). I'd suggest putting this all in a function that returns a string and that can then be called as needed to populate your textbox.