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

You can only set one value at a time. You'll need to repeat the nested IIf() to create the second field.

PS it is never too late to fix a poor table design. Since these checkboxes are mutually exclusive, you should have used a single field populated by a combo or an option group. Option groups work well for very small sets of data that are unlikely to change. Combos work well for larger sets of options or options that are likely to change.

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