Table field based on range of values 1

[NRK]

I have a field called MktCap which stores numbers (i.e. $250.00). What I would like is a way to associate the number with a text field next to it. To do this, I want Access to look at a table and determine if the value in MktCap(i.e. 250.00) falls in a range of numbers. Depending on the range, a text field is populated. For example, when I enter $250.00 the next field (MktCap_Type) is populated with "Small".

The MktCap_Type field is based on a table with the following fields - Min, Max, Type. The Min and the Max fields set the low and high ranges for the type. For example, Min is 0.00 and Max is 500.00 and type is Small.

My problem is that I know what I want Access to do, but I can't get there. Would my efforts be best accomplished in a query? I would like to have a table so that I can dynamically shift my Min and Max ranges for MktCap_Type.

I have tried experimenting with a number of different solutions, but have not come up with a good process. Any input would be greatly appreciated.

 

[GoDawgs]

You should pull this up in a query...first off I'd start by changing the field names from "Min" and "Max" to something else...those are reserved Access words I believe, might cause you headaches down the road.

Next just set up a query of your two tables that returns your MktCap_Type field...something like this:

SELECT MktCap, MktCap_Type
FROM TheMktCapTable, TheMktCap_TypeTable
WHERE ([MktCap] Between [MyMinField] And [MyMaxField]);

Hope that helps.

Kevin

 

[NRK]

Kevin -
That is exactly what I was looking for. Thanks much.

I also appreciate the reminder about using "Min" and "Max" for my field names.