functions in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
I doubt that you will ever get it to work with the following line:
Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,
[dbo_tblservice_activity]![units_activity_num]/60,

Also IIf statements don't really stop evaluating things until the end.
Therefore all the following could be true if the number is between 13 and 37.
That could cause problems.:
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 recommend using a Case statement in code which accepts Betweens.
 
IIF behaviour is different in SQL than in VB. Items are only
evaluated where the condition is met, so this is not a problem:

a: IIF(true,1,1/0)


But I agree that he can't have a sum inside an IIF. To get a
sum inside an IIF, he should write a subquery, or a VBA function,
or create a new field that is the sum.

(david)


Jerry Whittle said:
I doubt that you will ever get it to work with the following line:
Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,
[dbo_tblservice_activity]![units_activity_num]/60,

Also IIf statements don't really stop evaluating things until the end.
Therefore all the following could be true if the number is between 13 and
37.
That could cause problems.:
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 recommend using a Case statement in code which accepts Betweens.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


bindurajeesh said:
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.
Click to expand...
Click to expand...
 
I'll add that to my new things learnt list.

Thanks,
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


david epsom dot com dot au said:
IIF behaviour is different in SQL than in VB. Items are only
evaluated where the condition is met, so this is not a problem:

a: IIF(true,1,1/0)


But I agree that he can't have a sum inside an IIF. To get a
sum inside an IIF, he should write a subquery, or a VBA function,
or create a new field that is the sum.

(david)


Jerry Whittle said:
I doubt that you will ever get it to work with the following line:
Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,
[dbo_tblservice_activity]![units_activity_num]/60,

Also IIf statements don't really stop evaluating things until the end.
Therefore all the following could be true if the number is between 13 and
37.
That could cause problems.:
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 recommend using a Case statement in code which accepts Betweens.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


bindurajeesh said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top