T
ttrig324
Ok, I have a report that is tied to a query. This report works for
some jobs and for some it gives me this error:
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.
Here is the query:
PARAMETERS [Forms]![frmMain]![lstJobs] Value;
SELECT qprProdInvoices.CostID, qprProdJobCompRvn.ComponentID,
qprProdJobCompRvn.RecNo, qprProdJobCompRvn.Component,
qprProdJobCompRvn.JobID, qprProdJobCompRvn.ClientName,
qprProdInvoices.Date, qprProdInvoices.InvoiceNum,
qprProdInvoices.Total, qprProdInvoices.AdjInvTotal,
qprProdInvoices.Complete, qprProdInvoices.TPartyName,
qprProdInvoices.TPartyComm, qprProdInvoices.TPartyCD,
qprProdInvoices.TPartyPaid, qprProdInvoices.BalanceC,
qprProdInvoices.BalanceFin, IIf(qprProdInvoices!
AdjInvTotal>=0,qprProdInvoices!AdjInvTotal,
0)+IIf(qprProdAddServicesSum!SumAddAdjInvTot>=0,qprProdAddServicesSum!
SumAddAdjInvTot,0) AS InvToClient, IIf(Not IsNull([TPartyPaid]),
[TPartyPaid],0)+IIf(Not IsNull([BalanceFin]),[BalanceFin],0)+IIf(Not
IsNull([AddVenFinal]),[AddVenFinal],0) AS TPartyBalanceFin, IIf(Not
IsNull([SumJob]),[SumJob],0) AS SumMisJob, ([InvToClient])-
([TPartyBalanceFin]) AS GrossMarginMFG,
qprProdAddServicesSum.SumAddSSell,
qprProdAddServicesSum.SumAddAdjInvTot,
qprProdAddServicesSum.SumAddVendor, qprProdInvoices.CheckNum,
qprProdInvoices.CheckDate, qprProdInvoices.CheckAmt,
qprProdInvoices.CMAmtUsed, qprProdAddServicesSum.SumCMAmtUsed,
IIf(qprProdInvoices!CheckAmt<>0,qprProdInvoices!CheckAmt,"0.00")
+IIf(qprProdInvoices!CMAmtUsed<>0,qprProdInvoices!CMAmtUsed,"0.00")
+IIf(qprProdAddServicesSum!SumCheckAmt<>0,qprProdAddServicesSum!
SumCheckAmt,"0.00")+IIf(qprProdAddServicesSum!
SumCMAmtUsed<>0,qprProdAddServicesSum!SumCMAmtUsed,"0.00") AS
RevenueClient, IIf(qprProdInvoices!InvoiceCredit>=0,qprProdInvoices!
InvoiceCredit,"0.00")+IIf(qprProdAddServicesSum!
SumInvCredit>=0,qprProdAddServicesSum!SumInvCredit,"0.00") AS
InvCredits, IIf([RevenueClient]>=0,[RevenueClient],"0.00")-
IIf([InvToClient]>=0,[InvToClient],"0.00")+IIf([InvCredits]>=0,
[InvCredits],"0.00") AS BalanceRvn, qprProdInvoices.InvoicesDue,
qprProdInvoices.OutstInvoices, qprProdInvoices.BrefMfg,
qprProdInvoices.BrefFilm, qprProdInvoices.BrefFreight,
qprProdInvoices.RvnVoid, qprProdInvoices.InvRevised,
qprProdInvoices.CMDate, qprProdInvoices.CMTotal,
qprProdInvoices.CMDateUsed
FROM ((qprProdJobCompRvn LEFT JOIN qprProdInvoices ON
(qprProdJobCompRvn.RecNo=qprProdInvoices.RecNo) AND
(qprProdJobCompRvn.ComponentID=qprProdInvoices.ComponentID)) LEFT JOIN
qprProdAddServicesSum ON
(qprProdJobCompRvn.RecNo=qprProdAddServicesSum.RecNo) AND
(qprProdJobCompRvn.ComponentID=qprProdAddServicesSum.ComponentID))
LEFT JOIN qprProdMiscJobSum ON
qprProdJobCompRvn.JobID=qprProdMiscJobSum.JobID
WHERE (((qprProdJobCompRvn.JobID)=[Forms]![frmMain]![lstJobs]) And
((qprProdInvoices.InvoiceNum) Is Not Null)) Or
(((qprProdAddServicesSum.SumAddSSell) Is Not Null) And
((qprProdAddServicesSum.SumAddAdjInvTot) Is Not Null));
First let me say I did not write this query. I am just trying to get
it to work. This is just a report that calls this query as its
datasource. The weird thing is that the query will run fine if I go
to the query tab and double click this query and pass in the job #.
It pulls up all of the data fine. But if I run the report and let it
pass in the job # it gives me the error mentioned above. Now on some
jobs, when I run this report it works without a problem. I have run
the query from the tab and compared one that doesn't work against one
that does and I can not see anything that would make one work and the
other not. ANy ideas or suggestions are welcomed.
Thanks,
Tony
some jobs and for some it gives me this error:
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.
Here is the query:
PARAMETERS [Forms]![frmMain]![lstJobs] Value;
SELECT qprProdInvoices.CostID, qprProdJobCompRvn.ComponentID,
qprProdJobCompRvn.RecNo, qprProdJobCompRvn.Component,
qprProdJobCompRvn.JobID, qprProdJobCompRvn.ClientName,
qprProdInvoices.Date, qprProdInvoices.InvoiceNum,
qprProdInvoices.Total, qprProdInvoices.AdjInvTotal,
qprProdInvoices.Complete, qprProdInvoices.TPartyName,
qprProdInvoices.TPartyComm, qprProdInvoices.TPartyCD,
qprProdInvoices.TPartyPaid, qprProdInvoices.BalanceC,
qprProdInvoices.BalanceFin, IIf(qprProdInvoices!
AdjInvTotal>=0,qprProdInvoices!AdjInvTotal,
0)+IIf(qprProdAddServicesSum!SumAddAdjInvTot>=0,qprProdAddServicesSum!
SumAddAdjInvTot,0) AS InvToClient, IIf(Not IsNull([TPartyPaid]),
[TPartyPaid],0)+IIf(Not IsNull([BalanceFin]),[BalanceFin],0)+IIf(Not
IsNull([AddVenFinal]),[AddVenFinal],0) AS TPartyBalanceFin, IIf(Not
IsNull([SumJob]),[SumJob],0) AS SumMisJob, ([InvToClient])-
([TPartyBalanceFin]) AS GrossMarginMFG,
qprProdAddServicesSum.SumAddSSell,
qprProdAddServicesSum.SumAddAdjInvTot,
qprProdAddServicesSum.SumAddVendor, qprProdInvoices.CheckNum,
qprProdInvoices.CheckDate, qprProdInvoices.CheckAmt,
qprProdInvoices.CMAmtUsed, qprProdAddServicesSum.SumCMAmtUsed,
IIf(qprProdInvoices!CheckAmt<>0,qprProdInvoices!CheckAmt,"0.00")
+IIf(qprProdInvoices!CMAmtUsed<>0,qprProdInvoices!CMAmtUsed,"0.00")
+IIf(qprProdAddServicesSum!SumCheckAmt<>0,qprProdAddServicesSum!
SumCheckAmt,"0.00")+IIf(qprProdAddServicesSum!
SumCMAmtUsed<>0,qprProdAddServicesSum!SumCMAmtUsed,"0.00") AS
RevenueClient, IIf(qprProdInvoices!InvoiceCredit>=0,qprProdInvoices!
InvoiceCredit,"0.00")+IIf(qprProdAddServicesSum!
SumInvCredit>=0,qprProdAddServicesSum!SumInvCredit,"0.00") AS
InvCredits, IIf([RevenueClient]>=0,[RevenueClient],"0.00")-
IIf([InvToClient]>=0,[InvToClient],"0.00")+IIf([InvCredits]>=0,
[InvCredits],"0.00") AS BalanceRvn, qprProdInvoices.InvoicesDue,
qprProdInvoices.OutstInvoices, qprProdInvoices.BrefMfg,
qprProdInvoices.BrefFilm, qprProdInvoices.BrefFreight,
qprProdInvoices.RvnVoid, qprProdInvoices.InvRevised,
qprProdInvoices.CMDate, qprProdInvoices.CMTotal,
qprProdInvoices.CMDateUsed
FROM ((qprProdJobCompRvn LEFT JOIN qprProdInvoices ON
(qprProdJobCompRvn.RecNo=qprProdInvoices.RecNo) AND
(qprProdJobCompRvn.ComponentID=qprProdInvoices.ComponentID)) LEFT JOIN
qprProdAddServicesSum ON
(qprProdJobCompRvn.RecNo=qprProdAddServicesSum.RecNo) AND
(qprProdJobCompRvn.ComponentID=qprProdAddServicesSum.ComponentID))
LEFT JOIN qprProdMiscJobSum ON
qprProdJobCompRvn.JobID=qprProdMiscJobSum.JobID
WHERE (((qprProdJobCompRvn.JobID)=[Forms]![frmMain]![lstJobs]) And
((qprProdInvoices.InvoiceNum) Is Not Null)) Or
(((qprProdAddServicesSum.SumAddSSell) Is Not Null) And
((qprProdAddServicesSum.SumAddAdjInvTot) Is Not Null));
First let me say I did not write this query. I am just trying to get
it to work. This is just a report that calls this query as its
datasource. The weird thing is that the query will run fine if I go
to the query tab and double click this query and pass in the job #.
It pulls up all of the data fine. But if I run the report and let it
pass in the job # it gives me the error mentioned above. Now on some
jobs, when I run this report it works without a problem. I have run
the query from the tab and compared one that doesn't work against one
that does and I can not see anything that would make one work and the
other not. ANy ideas or suggestions are welcomed.
Thanks,
Tony