IIf statement with In

[r0nniem]

I'm wanting to look at everyday info (weekdays and weekend) for all Zones, except only weekdays for Newcastle, Gold Coast and Perth zones.

What I've tried in the query, is creating a new field with: iif([RptZoneCat]=(In ("Newcastle","Gold Coast","Perth")),8,[DOW]). It doesn't like it. Is there another way to write it?

I hope this is enought info you need. Can you please help me out?

 

[dhookom]

I don't agree with hard-coding values like this in expressions in queries. If something changes, you should not have to modify your expression buried in a query.

At the very least, you should create a small function that has arguments of RptZoneCat and DOW and returns either 8 or DOW. I would probably model this in my tables.

That said, you could try:
IIf(Instr("Newcastle~Gold Coast~Perth",[RptZoneCat])> 0,8,[DOW])

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]