Error #Num in a Query (1 Viewer)

[AnilBagga]

I have a query which looks like this. In the qryUncoatedFabCost some values of LamCode are Null and therefore values (numbers) fetched from qryCoatingRecipeHdr show error in such cases. I would like such values to be 0. I tried Nz() function to avoid this. No result.

How can one resolve this?

 

[theDBguy]

Hi. Sorry, but that doesn't really show us your query. Can you post the SQL statement instead? Thanks.

 

[Gasman]

Show the SQL?

 

[plog]

Agree with others about SQL, but I'm also scared to see it from the screenshot you posted.

1. You have an ID field in qryCoatingRecipeHdr but you are linking to CoatingCode from qryUncoatedFabCost. That is not typically how datasources with an ID field are joined.

2. You're building a 3rd query on 2 other queries. Not necessarily wrong but it sets off alarm bells in me. What is the SQL of those other 2 queries and why don't you build this query directly on the underlying tables?

 

[AnilBagga]

The SQL is as below of qryUncoatedFabCost

I am using 2 Totals queries in the qryCoatingRecipeHdr - a sum of recipe components (Q1A, Q2A...) and these queries (Q1A etc) use average values of exchange rates from another query. Doing all that in one query would not be feasible

Regarding linking coating code on ID fields, I am aware that this is not correct but my Access journey started with insufficient exposure and a lot of tables are not very well designed. Correcting this will take a huge effort and with my skill sets, perhaps corrupt the data

SELECT qryUncoatedFabCost.FGCode, qryUncoatedFabCost.FabCode, qryUncoatedFabCost.LamCode, qryUncoatedFabCost.TapeCost, qryUncoatedFabCost.UncoatedFabCostPerSQM, qryUncoatedFabCost.UncoatedFabCostPerKg, Nz([RecipeCostPerKg],0) AS RecipeKg, qryCoatingRecipeHdr.RecipeCostPerSQM, IIf([lamcode] Is Not Null,[TotalCostCoatingPerSQM],0) AS CoatingCostSQM, tblSubstrateMaster.Substrate AS S1, tblSubstrateMaster.CostRMID AS S1ID, tblSubstrateMaster_1.SubstrateID AS S2ID, tblSubstrateMaster_1.Substrate AS S2, tblCoatingValueAdd.WasteFabric, tblRewValueAddNWaste.RWValueAdd, tblRewValueAddNWaste.RWWaste, IIf([lamcode] Is Not Null,((([uncoatedfabcostpersqm]+[TotalCostCoatingPerSQM]+[S1CostSQM]+[S2CostSQM])*(1+Nz([wastefabric],0)))+Nz([rwvalueadd],0))*(1+Nz([rwwaste],0)),[uncoatedfabcostpersqm]) AS TotalCoatedFabSQM, [uncoatedgsm]+Nz([gsm-1],0)+Nz([GSM-2],0) AS TotalGSM, [TotalCoatedFabSQM]/(Nz([totalgsm],0)/1000) AS TotalCoatedFabKg, [qryAvgRM-MBRate].Rate AS S1Rate, [qryAvgRM-MBRate_1].Rate AS S2Rate, Nz(([SubstrateGSM1]/1000),0)*Nz([S1Rate],0) AS S1CostSQM, Nz(([SubstrateGSM2]/1000),0)*Nz([S2Rate],0) AS S2CostSQM, Nz([S1CostSQM],0)+Nz([S2CostSQM],0) AS SubstrateCostSQM, IIf(Nz([SubstrateGSM1],0)=0,0,Nz([S1CostSQM],0)*1000/(Nz([SubstrateGSM1],0)))+IIf(Nz([SubstrateGSM2],0)=0,0,Nz([S2CostSQM],0)*1000/(Nz([SubstrateGSM2],0))) AS SubstrateCostkg
FROM ((((((qryUncoatedFabCost LEFT JOIN qryCoatingRecipeHdr ON qryUncoatedFabCost.LamCode = qryCoatingRecipeHdr.CoatingCode) LEFT JOIN tblCoatingValueAdd ON qryCoatingRecipeHdr.ID = tblCoatingValueAdd.ID) LEFT JOIN tblRewValueAddNWaste ON qryUncoatedFabCost.RewWidthRange = tblRewValueAddNWaste.WidthRange) LEFT JOIN tblSubstrateMaster ON qryUncoatedFabCost.Substrate1 = tblSubstrateMaster.SubstrateID) LEFT JOIN tblSubstrateMaster AS tblSubstrateMaster_1 ON qryUncoatedFabCost.Substrate2 = tblSubstrateMaster_1.SubstrateID) LEFT JOIN [qryAvgRM-MBRate] ON tblSubstrateMaster.CostRMID = [qryAvgRM-MBRate].RMID) LEFT JOIN [qryAvgRM-MBRate] AS [qryAvgRM-MBRate_1] ON tblSubstrateMaster_1.CostRMID = [qryAvgRM-MBRate_1].RMID;

The sql of qryCoatingRecipeHdr is as under

SELECT qryCoatingSpecs.ID, qryCoatingSpecs.CoatingCode, qryCoatingSpecs.Extruder, qryCoatingSpecs.RM, qryCoatingSpecs.TorF, qryCoatingSpecs.[CoatingColor-1], qryCoatingSpecs.[CoatingColor-2], qryCoatingSpecs.NoOfSides, qryCoatingSpecs.[GSM-1A], qryCoatingSpecs.[GSM-1B], qryCoatingSpecs.[SplAdd-1], qryCoatingSpecs.[GSM-2A], qryCoatingSpecs.[GSM-2B], qryCoatingSpecs.[SplAdd-2], tblCoatingValueAdd.RM, tblCoatingValueAdd.ValueAdd AS Value1, tblCoatingValueAdd.WasteRM AS Waste1, tblCoatingValueAdd_1.ValueAdd AS Value2, tblCoatingValueAdd_1.WasteRM AS Waste2, Nz([GSM-1A],0)+Nz([gsm-1B],0) AS [GSM-1], Nz([GSM-2A],0)+Nz([gsm-2B],0) AS [GSM-2], ([gsm-1]*[value1]*Nz((1+[waste1]),0)+[gsm-2]*[value2]*Nz((1+[waste2]),0))/1000 AS ValueAddPerSQM, Nz(1+[tblcoatingvalueadd].[wasterm],0)*(Nz([1arecipe],0)*Nz([gsm-1a],0)+Nz([1Brecipe],0)*Nz([gsm-1B],0)+Nz([2arecipe],0)*Nz([gsm-2a],0)+Nz([2Brecipe],0)*Nz([gsm-2b],0))/(Nz([gsm-1a],0)+Nz([gsm-1B],0)+Nz([gsm-2a],0)+Nz([gsm-2b],0)) AS RecipeCostPerKg, (Nz([RecipeCostPerKg],0)/1000)*([gsm-1]+[gsm-2]) AS RecipeCostPerSQM, Nz([RecipeCostPerSQM],0)+Nz([ValueAddPerSQM],0) AS TotalCostCoatingPerSQM, qryCoatingSpecs.PDF
FROM (((((qryCoatingSpecs LEFT JOIN tblCoatingValueAdd ON (qryCoatingSpecs.RM = tblCoatingValueAdd.RM) AND (qryCoatingSpecs.[ColorCode1] = tblCoatingValueAdd.Color)) LEFT JOIN tblCoatingValueAdd AS tblCoatingValueAdd_1 ON (qryCoatingSpecs.RM = tblCoatingValueAdd_1.RM) AND (qryCoatingSpecs.ColorCode2 = tblCoatingValueAdd_1.Color)) LEFT JOIN Q1B ON qryCoatingSpecs.ID = Q1B.CoatingID) LEFT JOIN Q1A ON qryCoatingSpecs.ID = Q1A.CoatingID) LEFT JOIN Q2B ON qryCoatingSpecs.ID = Q2B.CoatingID) LEFT JOIN Q2A ON qryCoatingSpecs.ID = Q2A.CoatingID
ORDER BY qryCoatingSpecs.CoatingCode;

 

[arnelgp]

Correcting this will take a huge effort and with my skill sets, perhaps corrupt the data

you can do that if there is common relation to them.
otherwise, your computation is misleading and you think that it is always correct?

 

[AnilBagga]

you can do that if there is common relation to them.
otherwise, your computation is misleading and you think that it is always correct?

You are right.
However in relation to the problem originally posted, this will not resolve the situation as i would be linking 2 FK's anyway. The issue is the left join FK is null in some cases and therefore when you fetch any value from the right joined table/query, it shows an error! If there is a match, there is no issue and value is fetched correctly.

Is there anyway that I get a 0 value instead of the error?

 

[arnelgp]

just maybe,
create new query out of qryUncoatedFabCost:

select *, Nz([LamCode, 0) As NewLamCode from qryUncoatedFabCost;

use this query as a replacement for qryUncoatedFabCost in post#1.
link the other table/query using NewLamCode.

 

[AnilBagga]

just maybe,
create new query out of qryUncoatedFabCost:

select *, Nz([LamCode, 0) As NewLamCode from qryUncoatedFabCost;

use this query as a replacement for qryUncoatedFabCost in post#1.
link the other table/query using NewLamCode.

LamCode is a text field. I tried this NewLamCode: IIf([Lamcode] Is Not Null,[lamcode],"N/A") to avoid null fields, I do not get N/A in NewLamCode

The values I was fetching from the relationship were numeric and these numeric expressions were producing an error. I was trying Nz function for these numeric expressions trying to replace the #error values with 0