Query formatting problem

  • Thread starter Thread starter Dhulker
  • Start date Start date
D

Dhulker

I have a MS access database that keeps track of road segments for my work.
The road segments are stored in a table (along with another table that stores
project information) and I have a query that calculates length by subtracting
the beginning MP from the end MP. The query is fine for the most part except
for a few values that are not being calculated correctly. Here is the code:

SELECT PROJECTS.Number_ID AS PROJECTS_Number_ID, PROJECTS.OO_NUM AS
PROJECTS_OO_NUM, PROJECTS.PROJECT, PROJECTS.CO_NAME, PROJECTS.CO_NUM,
PROJECTS.DISTRICT, PROJECTS.Date, PROJECTS.COMMENT,
ROUTE_SEGMENTS.Auto_Number, ROUTE_SEGMENTS.Number_ID AS
ROUTE_SEGMENTS_Number_ID, ROUTE_SEGMENTS.OO_NUM
AS ROUTE_SEGMENTS_OO_NUM, ROUTE_SEGMENTS.PROJECT_NUM, ROUTE_SEGMENTS.N_LRS,
ROUTE_SEGMENTS.N_BMP,
ROUTE_SEGMENTS.N_EMP, ROUTE_SEGMENTS.N_COMMENT, ROUTE_SEGMENTS.R_LRS,
ROUTE_SEGMENTS.R_BMP, ROUTE_SEGMENTS.R_EMP, ROUTE_SEGMENTS.R_COMMENT,
PROJECTS.Initials, [O_EMP]-[O_BMP] AS OLD_LENGTH, [N_EMP]-[N_BMP] AS
NEW_LENGTH, [R_EMP]-[R_BMP] AS REDESIGNATED_LENGTH, ROUTE_SEGMENTS.SORT_ORDER
FROM PROJECTS INNER JOIN ROUTE_SEGMENTS ON PROJECTS.Number_ID =
ROUTE_SEGMENTS.Number_ID
WHERE (((PROJECTS.PROJECT)=[Enter the Project Number:]));

When I run the query for a certain project number I get this result:

Route BMP EMP Length
122-CR-0999 -010 0 21.469 21.469
122-CR-0999 -010 21.469 21.538 6.89999999999991E-02
122-CR-0999 -010 21.538 24.973 3.435

The first and third result seems fine but the second one is not formatted
correctly (I want it to show 0.068 on the query as well as the report that
uses it).
 
While that helps with the decimal places, the new result is displayed as a
currency (the database deals with road milepoints) and "#Error" displays when
null values are involved (and there will be null values in this query).

Jerry Whittle said:
Use something like below.

CCur([O_EMP]-[O_BMP]) AS ......
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Dhulker said:
I have a MS access database that keeps track of road segments for my work.
The road segments are stored in a table (along with another table that stores
project information) and I have a query that calculates length by subtracting
the beginning MP from the end MP. The query is fine for the most part except
for a few values that are not being calculated correctly. Here is the code:

SELECT PROJECTS.Number_ID AS PROJECTS_Number_ID, PROJECTS.OO_NUM AS
PROJECTS_OO_NUM, PROJECTS.PROJECT, PROJECTS.CO_NAME, PROJECTS.CO_NUM,
PROJECTS.DISTRICT, PROJECTS.Date, PROJECTS.COMMENT,
ROUTE_SEGMENTS.Auto_Number, ROUTE_SEGMENTS.Number_ID AS
ROUTE_SEGMENTS_Number_ID, ROUTE_SEGMENTS.OO_NUM
AS ROUTE_SEGMENTS_OO_NUM, ROUTE_SEGMENTS.PROJECT_NUM, ROUTE_SEGMENTS.N_LRS,
ROUTE_SEGMENTS.N_BMP,
ROUTE_SEGMENTS.N_EMP, ROUTE_SEGMENTS.N_COMMENT, ROUTE_SEGMENTS.R_LRS,
ROUTE_SEGMENTS.R_BMP, ROUTE_SEGMENTS.R_EMP, ROUTE_SEGMENTS.R_COMMENT,
PROJECTS.Initials, [O_EMP]-[O_BMP] AS OLD_LENGTH, [N_EMP]-[N_BMP] AS
NEW_LENGTH, [R_EMP]-[R_BMP] AS REDESIGNATED_LENGTH, ROUTE_SEGMENTS.SORT_ORDER
FROM PROJECTS INNER JOIN ROUTE_SEGMENTS ON PROJECTS.Number_ID =
ROUTE_SEGMENTS.Number_ID
WHERE (((PROJECTS.PROJECT)=[Enter the Project Number:]));

When I run the query for a certain project number I get this result:

Route BMP EMP Length
122-CR-0999 -010 0 21.469 21.469
122-CR-0999 -010 21.469 21.538 6.89999999999991E-02
122-CR-0999 -010 21.538 24.973 3.435

The first and third result seems fine but the second one is not formatted
correctly (I want it to show 0.068 on the query as well as the report that
uses it).
Click to expand...
Click to expand...
 
Although Now I can set the report to read it as a number, this effectively
solves my problem. Thank you for your help.
 
You could use the NZ function to convert nulls to 0s.

You could also use the Format command, but that would change the results to
a string instead of a number.

Format([O_EMP]-[O_BMP],"0.000")

If Format encounters a null, it returns a null.
 
Back
Top