expression typed incorrectly or too complex to be evaluated -sometimes

[arpeggione]

Hi: I have a query (which I would be happy to post if need be). SOMETIMES, I get the "expression typed incorrectly or too complex to be evaluated" error.

I have a main table "PROJECTS" in a one-to-many relationship with a table "CHANGEORDERS". If I run the query for a project that has records in "CHANGEORDERS", it works fine, but if there are no change orders - I get the error.

any thoughts or ideas?

thank you!!

karen

 

[TheAceMan1]

How are ya arpeggione . . .

We can't help without seeing the [blue]SQL[/blue] of the query! ... and don't type it, copy & paste into your next post.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[arpeggione]

Aceman - great for a Monday morning! How are you? Thx for your reply - here is the query (pasted straight from ACCESS):

SELECT tblProjectEngineers.ProjectEngrName, tblProjectEngineers.ProjectEngrID, tblProjectManagers.ProjectMgrName, tblProjectManagers.ProjectMgrID, tblCARReasons.Reason, tblWorkOrderNums.WorkOrderNum, tblWorkOrderNums.WorkOrderNumID, tblProjects.ProjectName, tblProjects.ProjectID, tblChangeOrders.CAR_ID, Val([CARNum]) AS myCARNum, tblChangeOrders.Designer, tblChangeOrders.Initiator, tblChangeOrders.CARType, tblChangeOrders.ScheduleChg, tblChangeOrders.BudgetChg, tblChangeOrders.ScopeChg, tblChangeOrders.ProposedChg, tblChangeOrders.ReasonID, tblChangeOrders.ReasonOtherText, tblChangeOrders.DateInitiated, tblChangeOrders.Cost, tblChangeOrders.AsPropOrAsMod, tblChangeOrders.OpSuperApprDate, tblChangeOrders.CMApprDate, tblChangeOrders.PMApprDate, tblChangeOrders.PEApprDate, tblChangeOrders.ConsultApprDate, tblChangeOrders.ContractorApprDate, tblChangeOrders.CODate, tblChangeOrders.CONum, tblChangeOrders.Comments, tblChangeOrders.Status, IIf(Not IsNull([CAR_ID]),DSum("[SchedImpact]","[tblAssoc_ChangeOrder_BContract]","[ChangeOrderID] =" & [CAR_ID]),0) AS mySchedImpact, IIf(Not IsNull([CAR_ID]),DSum("[Cost_$]","[tblAssoc_ChangeOrder_BContract]","[ChangeOrderID] =" & [CAR_ID]),0) AS myCost
FROM tblProjects LEFT JOIN (tblProjectEngineers RIGHT JOIN (tblProjectManagers RIGHT JOIN (tblCARReasons RIGHT JOIN (tblWorkOrderNums RIGHT JOIN tblChangeOrders ON tblWorkOrderNums.WorkOrderNumID=tblChangeOrders.WorkOrderNumID) ON tblCARReasons.ReasonID=tblChangeOrders.ReasonID) ON tblProjectManagers.ProjectMgrID=tblChangeOrders.PMApproverID) ON tblProjectEngineers.ProjectEngrID=tblChangeOrders.PEApproverID) ON tblProjects.ProjectID=tblChangeOrders.ProjectID
WHERE (((tblProjects.ProjectID)=Forms!frmEditCARs2011!subfrmEditCARLogCtnr.Form!txtEditProjectID))
ORDER BY Val([CARNum]);

I'm scratching my head on this one, but it is probably something simple that I'm not seeing....Karen

 

[dhookom]

I would try set the data type of Forms!frmEditCARs2011!subfrmEditCARLogCtnr.Form!txtEditProjectID in the Query->Parameters. If that didn't work, I would remove the DSum()s. If the records don't need to be editable, I would remove the DSum()s and replace them with LEFT JOINS to totals queries.

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Thanks all for your comments. I just figured out the problem - it wasn't the DSUMS....it was "myCARNum: Val(CARNum). I had to put an IIf(Not IsNull([CAR_ID]), myCARNum: val(CARNum)) - and....now it works!

Thanks again for having a look and kind regards, Karen

 

[arpeggione]

Duane: About the DSUMs, I've been reading further. Although my query does work, it might work better if I followed your advice and had separate totals queries that I joined to. Any comments?

thanks again...

karen

 

[dhookom]

Joining to totals queries would definitely be more efficient. However, the resulting recordset would not be updateable. A typical totals query would be:

SELECT [ChangeOrderID], Sum([Cost_$]) As SumCost
FROM tblAssoc_ChangeOrder_BContract
GROUP BY [ChangeOrderID]

You would then join the ChangeOrderID field to the CAR_ID field.

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

I see....thank you for your message.