Thread: IIF (Office 2000)

1. 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. 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. 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. 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. 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. 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. Re: IIF (Office 2000)

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

﻿IIf([FieldA]=

8. Re: IIF (Office 2000)

You could also try the Switch function:

Switch([FieldA]=

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

11. Re: IIF (Office 2000)

Where are you entering this?

12. 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. 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. 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. 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 Last

Posting Permissions

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