#Num! error in Right join Query

[AnilBagga]

I have a simple query where I use a right join. If an item is not found in the right joined table, it gives an error

Many values of Lamcode in the tblUncoatedFabCost are blank. In these cases I get a #Num! error in the query. How can I get 0 instead of the error as I need to use this value in a sum function?

Using Nz function on recipecostperkg did not help and I tried the IIF function as below. No help either!

I dont know VBA and use the QBE window in Design view to create queries

SELECT tblUncoatedFabCost.FGCode, tblUncoatedFabCost.FabCode, tblUncoatedFabCost.LamCode, tblUncoatedFabCost.UncoatedFabCostPerKg AS UFKG, tblUncoatedFabCost.UncoatedFabCostPerSQM AS UFSQM, IIf([recipecostperkg] Is Null,0,[recipecostperkg]) AS RKG FROM tblUncoatedFabCost LEFT JOIN qryCoatingRecipeHdr ON tblUncoatedFabCost.LamCode = qryCoatingRecipeHdr.CoatingCode;

 

[arnelgp]

... IIf(IsNull([recipecostperkg]), 0, [recipecostperkg]) AS RKG

 

[AnilBagga]

Same error. Modified SQL is as below

SELECT tblUncoatedFabCost.FGCode, tblUncoatedFabCost.FabCode, tblUncoatedFabCost.LamCode, tblUncoatedFabCost.UncoatedFabCostPerKg AS UFKG, tblUncoatedFabCost.UncoatedFabCostPerSQM AS UFSQM, IIf(IsNull([recipecostperkg]), 0, [recipecostperkg]) AS RKG
FROM tblUncoatedFabCost LEFT JOIN qryCoatingRecipeHdr ON tblUncoatedFabCost.LamCode = qryCoatingRecipeHdr.CoatingCode;

 

[theDBguy]

How about?

IIf([recipecostperkg] & ""="",0,[recipecostperkg]) AS RKG

 

[arnelgp]

can you use Val() function:

Val([recipecostperkg] & "") AS RKG

 

[AnilBagga]

[QUOTE="arnelgp, post: 1768302, member: 64504"] can you use Val() function: Val([recipecostperkg] & "") AS RKG [/QUOTE] [CODE]SELECT tblUncoatedFabCost.FGCode, tblUncoatedFabCost.FabCode, tblUncoatedFabCost.LamCode, tblUncoatedFabCost.UncoatedFabCostPerKg AS UFKG, tblUncoatedFabCost.UncoatedFabCostPerSQM AS UFSQM, Val([recipecostperkg] & "") AS RKG FROM tblUncoatedFabCost LEFT JOIN qryCoatingRecipeHdr ON tblUncoatedFabCost.LamCode = qryCoatingRecipeHdr.CoatingCode; [/CODE]

 

[AnilBagga]

How about?

IIf([recipecostperkg] & ""="",0,[recipecostperkg]) AS RKG

Does not help

I think the issue is the data in qryCoatingRecipeHdr.CoatingCode.

If I use tables in a mock qry as enclosed, I don't face any issue

 

[arnelgp]

its very hard to guess

IIF([recipecostperkg] & "" = "#num!", 0, [recipecostperkg]) as RGK

 

[Pat Hartman]

You didn't include the query with the issue. We have no idea what the hidden query is doing.