Query working with on parm but not another parm

J

jbruen

This query works when I enter 2006 but I'm receiving the folloing error when
I enter 2005 for a prompt:This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression by assigning parts
of the expression to variables. (Error 3071).

I have 4 expressions in the query since I want to create totals on the
report. The only way I know that I can do that is to add the field to the
query.

The expression between the @ is what is stopping it from working. I just
added that expression. I want to change the calculation based on the month
I'm processing. I want to add the "act" fields from the previous month and
"est" fields from the current and future months. The function is listed below.

SELECT TabEstimate.EstYear, TabProject.PROJECTMGR, TabProject.CRFNo,
TabProject.PROJECTNAME, Sum(TabEstimate.JanEst) AS SumOfJanEst,
Sum(TabActuals.JanAct) AS SumOfJanAct, Sum(TabEstimate.FebEst) AS
SumOfFebEst, Sum(TabActuals.FebAct) AS SumOfFebAct, Sum(TabEstimate.MarEst)
AS SumOfMarEst, Sum(TabActuals.MarAct) AS SumOfMarAct,
Sum(TabEstimate.AprEst) AS SumOfAprEst, Sum(TabActuals.AprAct) AS
SumOfAprAct, Sum(TabEstimate.MayEst) AS SumOfMayEst, Sum(TabActuals.MayAct)
AS SumOfMayAct, Sum(TabEstimate.JunEst) AS SumOfJunEst,
Sum(TabActuals.JunAct) AS SumOfJunAct, Sum(TabEstimate.JulEst) AS
SumOfJulEst, Sum(TabActuals.JulAct) AS SumOfJulAct, Sum(TabEstimate.AugEst)
AS SumOfAugEst, Sum(TabActuals.AugAct) AS SumOfAugAct,
Sum(TabEstimate.SepEst) AS SumOfSepEst, Sum(TabActuals.SepAct) AS
SumOfSepAct, Sum(TabEstimate.OctEst) AS SumOfOctEst, Sum(TabActuals.OctAct)
AS SumOfOctAct, Sum(TabEstimate.NovEst) AS SumOfNovEst,
Sum(TabActuals.NovAct) AS SumOfNovAct, Sum(TabEstimate.DecEst) AS
SumOfDecEst, Sum(TabActuals.DecAct) AS SumOfDecAct,
[SumOfJanEst]+[SumOfFebEst]+[SumOfMarEst]+[SumOfAprEst]+[SumOfMayEst]+[SumOfJunEst]+[SumOfJulEst]+[SumOfAugEst]+[SumOfSepEst]+[SumOfOctEst]+[SumOfNovEst]+[SumOfDecEst]
AS TotPlan,
[SumOfJanAct]+[SumOfFebAct]+[SumOfMarAct]+[SumOfAprAct]+[SumOfMayAct]+[SumOfJunAct]+[SumOfJulAct]+[SumOfAugAct]+[SumOfSepAct]+[SumOfOctAct]+[SumOfNovAct]+[SumOfDecAct]
AS TotAct, [TotPlan]-[TotAct] AS TotRemain, TabProject.ReportStatus,
TabProjectHours.Year,
@Sum(CalcNewHours([EstYear],[JanEst],[FebEst],[MarEst],[AprEst],[MayEst],[JunEst],[JulEst],[AugEst],[SepEst],[OctEst],[NovEst],[DecEst],[JanAct],[FebAct],[MarAct],[AprAct],[MayAct],[JunAct],[JulAct],[AugAct],[SepAct],[OctAct],[NovAct],[DecAct])) AS NewTotal @, First(TabProjectHours.ProjHrs) AS FirstOfProjHrs
FROM TabProject INNER JOIN (((TabEmployee INNER JOIN TabActuals ON
TabEmployee.Employee = TabActuals.UserName) INNER JOIN TabEstimate ON
(TabActuals.ActYear = TabEstimate.EstYear) AND (TabEmployee.Employee =
TabEstimate.UserName)) INNER JOIN TabProjectHours ON TabEstimate.EstYear =
TabProjectHours.Year) ON (TabProject.PROJECTNAME =
TabProjectHours.ProjectName) AND (TabProject.PROJECTNAME =
TabActuals.ProjectName) AND (TabProject.PROJECTNAME = TabEstimate.ProjectName)
GROUP BY TabEstimate.EstYear, TabProject.PROJECTMGR, TabProject.CRFNo,
TabProject.PROJECTNAME, TabProject.ReportStatus, TabProjectHours.Year
HAVING (((TabEstimate.EstYear)=[Forms]![FrmReportMenu]![YearCheck]) AND
((TabProject.CRFNo)<>"Admin" Or (TabProject.CRFNo) Is Null) AND
((TabProject.ReportStatus) Not Like "*Closed*") AND
((TabProjectHours.Year)=[Forms]![FrmReportMenu]![YearCheck]))
ORDER BY TabProject.CRFNo, TabProject.PROJECTNAME;


Function CalcNewHours(RptYear As Long, JanEst As Double, FebEst As Double,
MarEst As Double, AprEst As Double, MayEst As Double, _
JunEst As Double, JulEst As Double, AugEst As Double,
SepEst As Double, OctEst As Double, _
NovEst As Double, DecEst As Double, _
JanAct As Double, FebAct As Double, MarAct As Double,
AprAct As Double, MayAct As Double, _
JunAct As Double, JulAct As Double, AugAct As Double,
SepAct As Double, OctAct As Double, _
NovAct As Double, DecAct As Double) As Double
Dim MyNewTotal As Double
If Year(Now()) = RptYear Then
Select Case Month(Now())
Case 1
MyNewTotal = JanEst + FebEst + MarEst + AprEst + MayEst +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 2
MyNewTotal = JanAct + FebEst + MarEst + AprEst + MayEst +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 3
MyNewTotal = JanAct + FebAct + MarEst + AprEst + MayEst +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 4
MyNewTotal = JanAct + FebAct + MarAct + AprEst + MayEst +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 5
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayEst +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 6
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunEst
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 7
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulEst + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 8
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulAct + AugEst + SepEst + OctEst
+ NovEst + DecEst
Case 9
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulAct + AugAct + SepEst + OctEst
+ NovEst + DecEst
Case 10
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulAct + AugAct + SepAct + OctEst
+ NovEst + DecEst
Case 11
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulAct + AugAct + SepAct + OctAct
+ NovEst + DecEst
Case 12
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct +
JunAct
MyNewTotal = MyNewTotal + JulAct + AugAct + SepAct + OctAct
+ NovAct + DecEst
End Select
Else
MyNewTotal = JanAct + FebAct + MarAct + AprAct + MayAct + JunAct
MyNewTotal = MyNewTotal + JulAct + AugAct + SepAct + OctAct + NovAct
+ DecAct
End If
CalcNewHours = MyNewTotal
End Function

Any help would be appreciated.

Thanks in advance
John
 
Top