Query Help for a report

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
 
K

kingston via AccessMonster.com

Are you saying it always works when you open it from the queries tab? I
suspect that if it doesn't work via a report, it will not work as a query.
If this is the case and you can isolate an input that consistently causes the
query to fail, start dropping calculated fields or conditions one by one.
This will help pinpoint the problem. HTH

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
Click to expand...
 
T

ttrig324

Are you saying it always works when you open it from the queries tab? I
suspect that if it doesn't work via a report, it will not work as a query.
If this is the case and you can isolate an input that consistently causes the
query to fail, start dropping calculated fields or conditions one by one.
This will help pinpoint the problem. HTH





[email protected] said:
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:
Click to expand...
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.
Click to expand...
Here is the query:
Click to expand...
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));
Click to expand...
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.
Click to expand...
Thanks,
Tony
Click to expand...
Click to expand...

Yeah it will run every time if I run it from the query tab. But if I
run the report it will not run.
 
Top