Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    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

    W 0.200

    X 0.112

    Y 0.210

    Z 0.701

    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

    Appreciate any help you can provide

  2. #2
    Join Date
    May 2005
    Welcome to the forums

    Check out the ItemsSelected property of a listbox object. Martin Green has a pretty good tutorial on list boxes (including this property). Just loop through this collection and calculate your value.

    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.

    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts