access query

[wakin@fcswichita.org]

When I place the following in my query

ProductivityHou rs:
IIf([dbo_tblservice_ activity]![activity_sysid]=85,actualtimei ntounittime([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 actualtimeintou nittime([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.

[Larry Linson]

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@fcswichi ta.org> wrote in message
news:1142272771 .672080.29230@u 72g2000cwu.goog legroups.com...[color=blue]
> When I place the following in my query
>
> ProductivityHou rs:
> IIf([dbo_tblservice_ activity]![activity_sysid]=85,actualtimei ntounittime([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 actualtimeintou nittime([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]