| re: access query
Tablenames prefixed by "dbo_" are normal in SQL Server, but not in Access.
If your query is, in fact, an Access query, I'd suggest putting this logic
in a user-defined-function, so it can be written in a more user-friendly,
indented form. I don't write, nor do I try to analyze others'
multiply-nested IIFs, when there is any reasonable way to avoid it.
Larry Linson
Microsoft Access MVP
<wakin@fcswichita.org> wrote in message
news:1142272771.672080.29230@u72g2000cwu.googlegro ups.com...[color=blue]
> When I place the following in my query
>
> ProductivityHours:
> IIf([dbo_tblservice_activity]![activity_sysid]=85,actualtimeintounittime([units_activity_num]),IIf([dbo_tblservice_activity].[activity_sysid]=231,3,Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,[dbo_tblservice_activity]![units_activity_num]/60,IIf([dbo_tblservice_activity]![activity_sysid]=102
> Or [dbo_tblservice_activity]![activity_sysid]=158 Or
> [dbo_tblservice_activity]![activity_sysid]=219,0,IIf([dbo_tblservice_activity]![units_activity_num]>12
> And
> [dbo_tblservice_activity]![units_activity_num]<38,0.5,IIf([dbo_tblservice_activity]![units_activity_num]<63,1,IIf([dbo_tblservice_activity]![units_activity_num]<93,1.5,IIf([dbo_tblservice_activity]![units_activity_num]<123,2,IIf([dbo_tblservice_activity]![units_activity_num]<153,2.5,IIf([dbo_tblservice_activity]![units_activity_num]<181,3,0)))))))))))
>
> I get an error that indicates that this needs to be aggregated so when
> I group by it says that I can not have a sum in an aggregate. When I
> replace actualtimeintounittime([units_activity_num]), first line
> toward, end with a number the query runs just fine. This would be ok
> but I need to have a dynamic calculation in this place instead of a
> static number. Any wisdom would be appreciated. Might have to create
> sql server query and create a crystal report for this but really do not
> have the time to do that. Thanks.
>[/color]
| 
