Text in IIf(,,) expression in query

[KaiRich]

Hi all,

I have four fields, if any of them hold "Y" then I want
the query to return "Y" - but I only want one such field
in the query.

I have tried:
EMP: IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y"
Or [Emp Over]="Y","Y","N")
but get 'Not part of an aggregate function' error.
If this were numerical I would just throw Sum() around the
field names, but I can't find any text functions that will
do the same.

Can any one either point me to a text function that will
do the job, or let me know how to re-write the expression.

 

[Jeff Boyce]

So, you're saying that Sum([Field1] + [Field2] + ...) doesn't work? What
data type are the fields -- text or Yes/No?

If you get that error message, are you attempting an aggregate (Totals)
query?

Can you post the SQL statement of your query?

 

[Ragnar Midtskogen]

There is nothing wrong with the syntax in what you have posted, for each row
the IIf function will return either Y or N, depending on what is found in
those four fields.
The error must be due to something else in your query.
Be aware that if you are grouping on a field all the fields you select must
be part of an aggregate function.
For example, if you are the Count() or Sum() on the field you are grouping
on you need to use an aggregate function on any of the other fields you
select too. Often you can use First(0 or Last() if the fields you select
contain the same data.for all the records you count.or sum up.

Ragnar