Problem with #Error... 1

[Waxaholic]

I have a query where the source table occasionally has missing data. This is normal. The query returns #Error where the data would normally be if it were present in the source. I have tried using the IIF function to force the #Error to a value of "0" with no luck. No matter what i do to try and convert the #Error to something else it always returns #Error. I also tried the Replace function and it still returns #Error. Can someone lend me some insight into what may be causing this. I am so close to finishing this part of the database yet this issue has brought me to a halt.

Thank you,

Brian

 

[BlackKnight]

Hi,

When you say thta data is missing is normal you mean that it is null right?

Generally, the #error occurs when

1) You are using a NULL value in a calculation.
2) If a bound field on a form/report doesn't exist anymore in the underlying recordsource.

If you are using iif() or NZ() then you shouldn't be having a problem with calculations.

Are you using something like this in your query

CalcFld: iif(isnull([Fieldname]), 0, [FieldName])

Provide the query so we can check it out with you.

Have a good one!
BK

 

[Waxaholic]

Here is the SQL from the query in question:

SELECT Adjacencies2.Serving, Adjacencies2.Target, [Avg Serving RSSI 01].[Avg Serving RSSI 1]
FROM Adjacencies2 LEFT JOIN [Avg Serving RSSI 01] ON (Adjacencies2.Target = [Avg Serving RSSI 01].[Target 1]) AND (Adjacencies2.Serving = [Avg Serving RSSI 01].[Cell Site]);

I tried IIF(IsNull(.... but that also returns #Error.

If there is anything else you need just let me know. I appreciate the help.

Brian

 

[Waxaholic]

Here is the query by itself:

Expr1: IIf(IsNull([Avg Serving RSSI 1]),0,[Avg Serving RSSI 1])

Brian

 

[BlackKnight]

Hi,

I used the following query and it worked correctly:

SELECT Adjacencies2.Serving, Adjacencies2.Target, IIf(IsNull([Avg Serving RSSI 1]),0,[Avg Serving RSSI 1]) as AvgRSS
FROM Adjacencies2 LEFT JOIN [Avg Serving RSSI 01] ON (Adjacencies2.Target = [Avg Serving RSSI 01].[Target 1]) AND (Adjacencies2.Serving = [Avg Serving RSSI 01].[Cell Site]);

Have a good one!
BK

 

[BlackKnight]

Hi again,

if it give you #error still then check the table itself and see if the all the fields are spelleced correctky and exist. If you are connecting to a different data DB then ensure that one has the fields. It has to be something small.

Have a good one!
BK

 

[Waxaholic]

Thank you BlackKnight. I was fiddling with this all night and came to the conclusion that for some reason, the LEFT Join is causing the problem. I tried the mod to the SQL you included above but i still get the #Error. I then took a new approach to the problem. I split the source into 2 seperate parts and created a UNION Query to bring them back together. As a temporary workaround. This took care of the #Error i was recieving. I might stick with this if it proves not to be burdensome to the DB. I appreciate your help on this issue. If i find the cause of the problem and a fix, i will post it here in the event someone else experiences this.

Thanks,

Brian