use a rate from combo box in calcuation on query

  • Thread starter Thread starter Barb
  • Start date Start date
B

Barb

I need to use a value(rate) from a combo box in my query
for calculations. I know it is usually better to put the
combo box on the form. I need to use the rate in
numberous calcuations and would rather they all be in my
query so I can use them on multiple reports and forms. I
have problems with makeing the lookup wizard in the
table. The original table of rates has the rates as
currency. Is that a problem. I would like the drop down
to show county, state and rate and display and store the
rate as currency. Any tricks. or what to watch out
for. Please help.
 
Combo box behavior is controlled by the following
Properties, whose values you can examine in Form Design
View by 1) selecting View, Properties; 2) pressing <F4>;
or 3) Right-clicking on your mouse:

ROW SOURCE TYPE Table/Query, Value List, or Field List
ROW SOURCE An SQL Statement that selects the columns to
be displayed in the combo box
BOUND COLUMN The column whose value is stored in the
field specified in the CONTROL SOURCE property

So, if you'd like the currency value stored, set the BOUND
COLUMN property to its corresponding column number, and
set the CONTROL SOURCE to store this value in the
appropriate field of the table on which the form is based.

To do a calculation in a query, simply refer to this
field, e.g., assuming its name is Rate, something like:

[Tax]: [Price]*[Rate]

Your question hints at a frequent source of confusion many
new Access users have--the distinction between a CONTROL
and a FIELD. Controls merely display data on a form; the
data is stored in FIELDS in TABLES. A combo box can be
additionally confusing, because if the first column's
Column Width is set to 0", and it is the Bound Column, the
2nd non-zero-length column will display after the
selection, but the first one will be stored in the table.

So, you don't do a calculation on a combo box value in a
query, you do it on the table's field in which the combo
box value was stored.

Hope that helps.

Kevin Sprinkel
 
Back
Top