# Thread: Index in a Query?? (Office 2000)

1. ## Index in a Query?? (Office 2000)

Hi,

I have a field (Field A) that always has the numbers 1 thru 8 in it.

The next field is just a numeric field 1 thru 100 (Field [img]/forums/images/smilies/cool.gif[/img].

In the field I would like to create (Field C) I need it to add "Field B to a calculated number from (Field A).

That calculated number would be ( IF the number in "Field A" is 1 then it would be 0 -- OR if the number in "Field A"is 2 then it would be .14 -- and so on to number 8)

1=0
2=.14
3=.21
4=.28
5=.35
6=.42
7=.49
8=.56

So if Field A was 3 and Field B was 32 then Field C would be 32.21 (Field B + Calculated number from Field A)

Is there a way to do this ??

Bill

2. ## Re: Index in a Query?? (Office 2000)

Yes, there is a way to do that. The simplest way would be a query with an expression. It would involve a series of nested IIF statements for each of the 8 possibilities. A bit more elegant way would be to write a small standard module the used a Select Case statement and returned the new value. If you need to save the result permanently, which isn't usually necessary, you could create a separate field and use an update query.

3. ## Re: Index in a Query?? (Office 2000)

The little-known Choose function is perfect for this. In a query, use<pre>FieldC:[FieldB]+Choose([FieldA],0,.14,.21,.28,.35,.42,.49,.56)</pre>

If you want a text box on a form to display the calculated value, set its control source to<pre>=[FieldB]+Choose([FieldA],0,.14,.21,.28,.35,.42,.49,.56)</pre>

You can also use the linear progression of the values (except for the first one):<pre>FieldC:IIf([FieldA]=1,0,[FieldA]*.07)</pre>

but of course, this will fall down if the values change to something less regular.

4. ## Re: Index in a Query?? (Office 2000)

Hi Wendell,

If I wanted to use the IIf and save the query is that still OK?
Could you tell me how to string the IIf toghether.
Bill

5. ## Re: Index in a Query?? (Office 2000)

Hi Hans,

This works great, thank you very much.

I have never heard of the Choose function. I have 3 books on Access 1- pretty basic and 2-of them are over a thousand pages each and there isn't any mention of it that I can find. The only information I found was in Access help.

Am I understanding this right ? with this function you can use 1 through 29 and whatever you place first will be matched up with number 1. "Choose([FieldA],0,.14,.21,.28,.35,.42,.49,.56)"

Thanks again,
Bill

6. ## Re: Index in a Query?? (Office 2000)

Say, you have Choose(index, value1, value2, value3, value4). If index =1, this will return value1; if index = 2, this will return value2 etc. If index is less than 1 or greater than the number of items to choose from, the function will return Null.

The index and the values can also be expressions, but using complicated expressions for the values can be inefficient, for *all* values are evaluated each time the function is called, not just the "chosen" value.

7. ## Re: Index in a Query?? (Office 2000)

Actually, Hans' suggestion about the Choose function is a better choice than nested IIF statements, but in general it would look like:

CalcValue: IIF([FieldA]=1,0,IIF([FieldA]=2,0.14,IIF([FieldA]=2,0.28,IIF(....))))

8. ## Re: Index in a Query?? (Office 2000)

Hi Wendell,

Thanks for the answer, I will save it for future use.
I did use Choose function that Hans suggested, it works great.

I really can't even begin to tell you how much I appreciate both your and Hans help.

Bill

9. ## Re: Index in a Query?? (Office 2000)

Look up the Switch function as well. It allows you to evaluate different conditions and use a matching value for that condition. So a typical Switch expression might be something like:

Switch([Field1] = "A",Yes, [Field2 = "B"], Yes, [Field1]="B" AND [Field2]="A" ,No)

What that says is return Yes (True) if the value in Field1 = "A" or the value in Field2 = "B" and if Field1 = "B" and Field2 = "A" return No, otherwise return nothing (Null)..

#### Posting Permissions

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