Help me with this error?

[oozze]

Good Morning all,

Please disregards the tables/queries naming. I did that so i can easier to locate, but after feeling good about it, I will fix it.

So this below query is working for me for the quarter (since including larger jobs). However, when I do small list of jobs, I am encountering error.

The reason being that my material table (03a Mat-Sum) dont have [03a Mat-Sum].BT.
However, I really want to put as a placecard on this query for the column placement purposes.

How can i get around this?

Thank you.



SELECT [00 Sheet1].JobID, Job_List_all.CustomerName, Job_List_all.SystemSize, Format([StartDate],"mm/dd/yyyy") AS StartDte, Format([enddate],"dd/mm/yyyy") AS EndDte, Job_List_all.STATUS, Job_List_all.substatusname, Job_List_all.IsActive, Job_List_all.Office, Job_List_all.PaymentTypeName, Job_List_all.TrancheName, Job_List_all.MarketTypeName, Job_List_all.ProductTypeName, Job_List_all.InspectionCompleteDate, Job_List_all.PermitStatus, Job_List_all.ConstructionTypeName, Job_List_all.Type AS [Roof Type], [03d Status-Sheet1].[MO Status], Job_List_all.STATE, [03a Mat-Sum].PV, [03a Mat-Sum].[IN], [03a Mat-Sum].MH, [03a Mat-Sum].EE, [03a Mat-Sum].ME, [03a Mat-Sum].RM, [03a Mat-Sum].BT AS Expr1, [03a Mat-Sum].IE, [03a Mat-Sum].EF, [03a Mat-Sum].[Total Material], [03b Lab-Sum].SumofWageBenf AS LaborwBenf, [03c 5xxx-Sum].SumOfItemCost AS JobSpecific, Format(Nz([03a Mat-Sum].[Total Material],0)+Nz([03b Lab-Sum].[SumofWageBenf],0)+Nz([03c 5xxx-sum].[SumofItemcost],0),"Standard") AS [Total Cost] INTO [00 Job Costing]
FROM (((([00 Sheet1] LEFT JOIN [03a Mat-Sum] ON [00 Sheet1].JobID = [03a Mat-Sum].JobID) LEFT JOIN [03b Lab-Sum] ON [00 Sheet1].JobID = [03b Lab-Sum].JobID) LEFT JOIN [03c 5xxx-Sum] ON [00 Sheet1].JobID = [03c 5xxx-Sum].JobID) LEFT JOIN [03d Status-Sheet1] ON [00 Sheet1].JobID = [03d Status-Sheet1].JobID) LEFT JOIN Job_List_all ON [00 Sheet1].JobID = Job_List_all.JobID;

 

[spikepl]

You need to make up your mind - either you have a field in your data that you want to query on, or not. If you do not have that field in your data then you need to make a dedicated query for that type of data where you can put, for example,

Null AS Expr1

And goodnight to you, btw. Since the sun never sets on AWF, then mornings/evenings are a rather relative experience.

Update :

Since yo are a newbie:

ALl oposts are about HEelp, so mentionieng that is not useful. "this error" is also not descriptive. When asking for help, state the issuu as well as you can. This allows people quickly to see if the post is relevant for them and to make use of "Similar Issues" at the bottom of each page, which is based on titles

 

[oozze]

Thank you spikepl...and good night to you.