Access Report Issue since upgrading to 2007

[Dhulker]

I created a report in Access 2003 that used a query to calculate the length
of road segments by subtracting the beginning mile-point from the end
mile-point. Not all road segments had mile-points assigned to them so some
of them had null values. This did not create an error message until our
office upgraded to access 2007. When that happened, every-time the report
ran it would state "#Error" whenever there was no value. How do I get rid of
such messages?

 

[Clifford Bass via AccessMonster.com]

Hi,

What do you want to happen when one or both of the end points are
missing? You can use the Nz() function to deal with null values on either
size of the equation by converting them to actual numerical values. Maybe:

Nz(EndPoint, 0) - Nz(StartPoint, 0)

However, with that you might end up with bad lengths. So if EndPoint is
35 and there is no StartPoint, you end up with 35. Or with the reverse you
end up with -35. If the lack of an end point indicates less than a mile,
perhaps:

Nz(EndPoint, Nz(StartPoint, 0)) - Nz(StartPoint, Nz(EndPoint, 0))

This would use the start point when there is no end point and the end
point when there is no start point. If neither point is there, it just uses
0 - 0 which results in 0.

Hope that helps,

Clifford Bass

 

[Clifford Bass via AccessMonster.com]

Hi,

Further, you may want to use the Abs() function so you always get
positive numbers:

Abs(Nz(...) - Nz(....))

Clifford Bass