Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF (Office 2000)

    Hi,

    Can someone tell me how to do the following?

    "Field A" displays 5/16 - I need "Field B" to diplay 1650 when "Field A'" is 5/16

    Thanks,
    Bill

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

    Re: IIF (Office 2000)

    Use the following expression: Mid([Field A],InStr([Field A],"/")+1) & Left(Left([Field A],InStr([Field A],"/")-1) & "00",2)

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

    Re: IIF (Office 2000)

    Hi Hans,

    Thanks for your help but I must be missing something, I tried the expression but it came up in #error.

    Am I wrong in thinking that you could use IIf?

    Thanks,
    Bill

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF (Office 2000)

    If FieldA is a field in a table, you could create a query based on the table and create a calculated field in the query called FieldB

    If fieldA is numeric

    FieldB:IIf([FieldA]=.3125,1650,0)

    If FieldA is text
    IIf([FieldA]="5/16",1650,0)

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

    Re: IIF (Office 2000)

    The expression works in the sample I whipped. I assumed you understood that "Field A" has to be replaced by the actual field name.

    But perhaps I completely misunderstood your question - the way Tom Goodwin interprets it is very different from my reading of it. Can you try to explain more precisely what you want?

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

    Re: IIF (Office 2000)

    Hi Tom,

    Thanks the text version works great. If you have time could you tell how to extent this out as follows/

    1st IIf([FieldA]="5/16",1650,0)
    2nd IIf([FieldA]="1650",1650,0)
    3rd IIf([FieldA]="3/8",1980,0)

    Thanks,
    Bill

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF (Office 2000)

    Based on you reply FieldA appears to be text and FieldB is numeric. One solution would be:

    IIf([FieldA]=

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF (Office 2000)

    You could also try the Switch function:

    Switch([FieldA]=

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

    Re: IIF (Office 2000)

    Hi Tom and Pat,

    Thanks for your help.
    I tried to enter both formulas but each time I tried I kept getting the following message.

    Invalid syntax
    You have entered an operand without an operator

    Can you tell me how to fix this?

    Thanks,
    Bill

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF (Office 2000)

    Where exactly are you trying to use this formula? The operator could be a "=" or something else, so you'll need to provide more information, not just the text of the error message.
    Charlotte

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF (Office 2000)

    Where are you entering this?

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

    Re: IIF (Office 2000)

    Hi Patt,

    Right now I'm using it in a query in the field box like this "ADist: IIf([Distance]="5-16",1650,1650)" and this works as long as I pick the criteria "5-16" Or "1650"

    But when I use the new Switch or IIF formulas I get an error, I put Distance in the place of Field A.

    Thanks,
    Bill

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF (Office 2000)

    What's the error?
    Why don't you post the SQL of the query here so we can have a look at it?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: IIF (Office 2000)

    Hi Patt,

    The error is.
    Invalid syntax
    You have entered an operand without an operator

    Heres the SQL, thanks for your help.
    Bill

    SELECT A_OUTINGS_FINAL.Track, Grant_1_thru_8.Dog_Name1, A_OUTINGS_FINAL.Distance, A_OUTINGS_FINAL.Post, A_OUTINGS_FINAL.ART, IIf([Distance]="5-16",1650,1650) AS ADist, [ADist]/[ART] AS FPS, [FPS]*Choose([Post],1,1.00057,1.002281,1.00171,1.003421,1.003991,1.00 3421,1.002281) AS AFPS, DAvg("AFPS","Win Probabilities Dog #1 Query") AS AvgAFPS, DStDev("AFPS","Win Probabilities Dog #1 Query") AS StDevFPS, ([AFPS]-[AvgAFPS])/[StDevFPS] AS ZScore
    FROM A_OUTINGS_FINAL INNER JOIN Grant_1_thru_8 ON A_OUTINGS_FINAL.Dog_Name = Grant_1_thru_8.Dog_Name1
    WHERE (((A_OUTINGS_FINAL.Track)="GG") AND ((A_OUTINGS_FINAL.Distance)="5-16" Or (A_OUTINGS_FINAL.Distance)="1650"));

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF (Office 2000)

    Hi Bill
    What I would do is to omit all the functions in the query and introduce them, one at a time, and see where the problem is.
    If you don't get any joy there, maybe post the DB here so we can all have a look, take out the sensitive stuff etc.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Page 1 of 2 12 LastLast

Posting Permissions

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