Can I create multiple values based on an iif atatement?

[tcaprice]

I currently have an iif statement that creates a value in a new field name based on a value in an existing field name.

Example:

SELECT [EMRB MASTER].LMPID, [EMRB MASTER].[Last Name], IIf([EMRB MASTER].[SPMES Promotion]=-1,"SPMES") AS CURRJOB
FROM [EMRB MASTER];

if the SPMES Promotion field has been checked, I set the value of a new field called CURRJOB to "SPMES".

What I want to do is, based on this iif statement, ALSO set the value of a second new field called PROMODATE to the value in an existing field called "SPMES Promo".

Can I set values in multiple fields this way? This is actually just 1 iif statment of a multiple iff (6 iif's) and on each iif i need to assign the multiple values.

As always, help is much apprecciated.

Thanks.

Tom

 

[tcaprice]

Pat,

You are right about the poor table design. I have been resisting going back and redesigning but it's become pretty evident from the reporting requirements that a table redesign is in order. I could have gotten by if I could assign more than 1 value at a time in the iif but based on your input I see no other way than to rework this.

Thanks for the dose of reality and taking the time to reply back.

Tom