Custom field in a query

[ZBKAI189]

Can a custom field in a query return 'A' if condition 1 is met and 'B' if condition 1 isn't met? If title="supervisor" then run equation 'x'... if title does not ="supervisor" then do not run equation 'x'.

Make sense?

 

[pbaldy]

Sure; try an IIf() function:

IIf(title="supervisor", "A", "B")

 

[ZBKAI189]

Ok... sounds easy enough =/

Right now I have "DUE_MXG: DateAdd("m",-1,[proj_eval_close_date])"

But I only want that equation is title=supervisor

If title does not=supervisor then I need it to display N/A or something along those lines.

Idea?

 

[pbaldy]

You'd replace "A" with the DateAdd equation, and "B" with "N/A".

 

[ZBKAI189]

I've got:

DUE_MXG: IIf([LOCAL].[GRADE]="MSG" Or "SMS" Or "CMS",DateAdd("m",-1,[proj_eval_close_date]),"N/A")

But when I run the query I get the dateadd for all records... even if the requirement isn't met (local.grade="msg" or "sms" or "cms")

 

[pbaldy]

That's different than your original request, but in any case you have to repeat the field:

IIf([LOCAL].[GRADE]="MSG" Or [LOCAL].[GRADE]="SMS" Or [LOCAL].[GRADE]="CMS",...

 

[ZBKAI189]

WOO HOO! It works... thank you!!

 

[pbaldy]

Yippee! Glad we got it sorted out.

 

[ZBKAI189]

Oh wait... almost. It returns the correct information... but any idea why I can't get the dates to display as medium date? I've got all formats set to medium... but it displays as short.

 

[pbaldy]

I would generally format it in the form or report the user will see, but that might be screwed up here because of the mix of date and text values. You may need to wrap the DateAdd() function in the Format() function.