If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Text in IIf(,,) expression in query
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. |
#2
|
|||
|
|||
Text in IIf(,,) expression in query
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? -- More info, please ... Jeff Boyce Access MVP |
#3
|
|||
|
|||
Text in IIf(,,) expression in query
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 |
Thread Tools | |
Display Modes | |
|
|