#Error

[D Willett]

It's been a while since I worked on any Access queries !

The following returns #Error in the [Parts]/[Labour] calculated field:

SELECT [qryEstimatorStats-Dash].Estimator, [qryEstimatorStats-Dash].Parts, [qryEstimatorStats-Dash].Labour, [Parts]/[Labour] AS [Parts-Lab]
FROM [qryEstimatorStats-Dash];

Can some one refresh my memory :-)

Regards

[HansV]

Are Parts and Labour both number (or currency) fields?

Does it make a difference if you use

SELECT [qryEstimatorStats-Dash].Estimator, [qryEstimatorStats-Dash].Parts, [qryEstimatorStats-Dash].Labour, [qryEstimatorStats-Dash].[Parts]/[qryEstimatorStats-Dash].[Labour] AS [Parts-Lab]
FROM [qryEstimatorStats-Dash];

[D Willett]

They're both number Hans as far as I can see, your version doesn't work either.

Regards

[HansV]

Hmm - null (blank) values in Parts or Labour should result in null values in Parts-Lab, and zeros in Labour should result in #Div/0, not in #Error, so it's hard to tell what causes this.

Could you post a stripped-down and zipped copy of the database without sensitive information?

[D Willett]

Hi Hans

Th equery takes its parts from nested queries so probaby too cumbersome to send a demo. I'll check the queries from the bottom up and see where the problem lies. I'll come back and let you know how I get on.
Just thought this could have been an easy fix... nothings simple is it lol.

Regards

[HansV]

The SQL itself is OK. I'd start by opening qryEstimatorStats-Dash and inspecting the Parts and Labour columns for anomalies.

[D Willett]

Cheers Hans
It might be easier to start afresh with the query and do away with the underlying ones.

Kind Regards

[HansV]

OK, good luck. Post back if you need help.

[D Willett]

Hi Hans

It seems the parts and labour elements are "text" in the table design. As this table is used extensively I cannot change the property !


SELECT tblEST.Finished, SplitPart([EST_NME],1) AS Estimator, tblEST.STA, Val([PRT_CST]) AS Parts, Val([LAB_CST]) AS Labour, Val([parts]/[labour]) AS Parts_To_Labour
FROM tblEST
WHERE (((tblEST.STA)="F"));

The #Error only occurs when 0 divides into 0 ?

[HansV]

Try

IIf(Val([Labour])=0,Null,Val([Parts])/Val([Labour])) AS Parts_To_Labour

[D Willett]

Thanks Hans.

I Modified slightly:

IIf(Val([Labour])=0,0,Val([Parts])/Val([Labour])) AS Parts_To_Labour

Instead of Null which serves the purpose better.

Regards

[HansV]

That's fine - you can use whatever value suits your purpose.