A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access Report Issue since upgrading to 2007



 
 
Thread Tools Display Modes
  #1  
Old December 30th, 2009, 09:37 PM posted to microsoft.public.access.reports
Dhulker
external usenet poster
 
Posts: 7
Default Access Report Issue since upgrading to 2007

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?
  #2  
Old January 5th, 2010, 01:47 AM posted to microsoft.public.access.reports
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default Access Report Issue since upgrading to 2007

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

Dhulker wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201001/1

  #3  
Old January 5th, 2010, 01:50 AM posted to microsoft.public.access.reports
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default Access Report Issue since upgrading to 2007

Hi,

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

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

Clifford Bass

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201001/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.