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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query formatting problem
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). |
#2
|
|||
|
|||
Query formatting problem
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" wrote: 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). |
#3
|
|||
|
|||
Query formatting problem
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" wrote: 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" wrote: 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). |
#4
|
|||
|
|||
Query formatting problem
Although Now I can set the report to read it as a number, this effectively
solves my problem. Thank you for your help. |
#5
|
|||
|
|||
Query formatting problem
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Dhulker" wrote: Although Now I can set the report to read it as a number, this effectively solves my problem. Thank you for your help. |
Thread Tools | |
Display Modes | |
|
|