Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 ??

    Thanks for your help.

    Bill

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index in a Query?? (Office 2000)

    Hi Wendell,

    Thanks for your help.
    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. #5
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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(....))))
    Wendell

  8. #8
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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)..
    Charlotte

Posting Permissions

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