Join iif's in Query 2

[lars7]

Hi Guys,

How do you join 2 iif's in a query:

Disc: IIf([Discipline]="PTA","AHP's",[Discipline])=IIf([Discipline]="PTB","Technical",[Discipline])

I tried this but got -1 and 0

 

[Crowley16]

join 2 iifs? how do you mean join?

what you've got there is a comparison between the 2 iifs

p.s. in access -1 is the numeric value of true and 0 is false

--------------------
Procrastinate Now!

 

[lars7]

Hi Crowley16,

Sorry for my poor description of what I want to do.

I would like change the word "PTA" in my query to "AHP'S" and this works:

Disc: IIf([Discipline]="PTA","AHP's",[Discipline])

But I would also like to say:

IIf([Discipline]="PTB","Technical",[Discipline])

I tried "and" and "&" to link the 2 but that didn't work either any help you can give with this would be appreciated.

 

[PHV]

Disc: IIf([Discipline]="PTA","AHP's",IIf([Discipline]="PTB","Technical",[Discipline]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

you need to nest the iifs...

i.e.

iif (
discipline = "PTA",
"AHP's",
iif (
discipline = "PTB",
"Technical",
discipline
)
)

--------------------
Procrastinate Now!

 

[lars7]

Thanks PH,
I had to add a third discipline into it and that worked fine too.

Disc: IIf([Discipline]="PTA","AHP's",IIf([Discipline]="PTA (UNTR.)","AHP's",IIf([Discipline]="PTB","TECHNICALS",[Discipline])))

 

[dhookom]

IMHO hard-coding values like this into nested IIf()s isn't good practice. I would probably create a solution that involves a table of disciplines with the values required.

I think the next best solution would be a small user-defined function that accepts the Discipline as an argument and returns the Disc value. The function would allow you to maintain the "lookups" in a single place and the function could be used in control sources, queries, code,...

I try not to "define" and "maintain" relationships like this in query designs. When business rules change, you shouldn't have to change your query designs.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlexCuse]

Great point Duane. Here is an article I really like about this very subject (written for SQL Server, but the same ideas apply). For the uninitiated, the CASE statement in SQL is equivalent to the iif in access.

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

Alex

Ignorance of certain subjects is a great part of wisdom

 

[dhookom]

Alex,
Great link, it's marked in my favorites for future reference. Any time I see numbers or strings hard-coded, it raises a red flag.

I was just talking with my brother yesterday regarding some data he was working on at his company. He was asked to make some major changes that would improve their functionality in an application. He said it only took him a few minutes to run an update query (no coding or designing, just data updates). I told him he should have complained about how it would take him 6 hours of complete isolation from all other tasks and employees. He could then run the query and listen to music or play some golf for the remainder of the time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlexCuse]

He could then run the query and listen to music or play some golf for the remainder of the time.

When I first started where I currently work, there were some DB's where it would take about 3-4 hours to compact (depending on network traffic) when you closed them. So at the time, that was not too far from what was going on

Ignorance of certain subjects is a great part of wisdom