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).
0
Utf
12/17/2009 5:59:03 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
578 Views

Similar Articles

[PageSpeed] 41

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).
0
Utf
12/17/2009 6:21:01 PM
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).
0
Utf
12/17/2009 6:41:02 PM
Although Now I can set the report to read it as a number, this effectively 
solves my problem.  Thank you for your help.
0
Utf
12/17/2009 6:45:01 PM
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.
0
Utf
12/17/2009 8:11:02 PM
Reply:

Similar Artilces:

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Problem with message routing between EXCH2K and EX2K3
We are running a ver simply Exchange topology, with an Exchange 2000 single server (ServerA) acting as "dumb" Information Store. Incoming and outbound SMTP email is relayed through a Linux based SMTP host relay on the same subnet, which handles all domain lookups for mail routing. ServerA is configured to use to the Linux SMTP server as it's smart host. The domain is a native Windows 2003 AD topology. My problem is the following: I have recently added a new Exchange 2003 Server (ServerB) into our Exchange site topology. If I create a user (UserA) on ServerA and then do a...

Conitional Formatting
Hello. I have two fields in a subform, "User" and "IT Announcement" I would like to do conditional formatting to this effect: On ther "User" field: If "IT Announcement" = Yes then make the "User" field turn blue (I would choose the color blue from the conditional formatting selection. How would you write this? Thanks. Iram/mcp On Tue, 23 Oct 2007 14:59:01 -0700, Iram wrote: > Hello. > I have two fields in a subform, "User" and "IT Announcement" > I would like to do conditional formatting to this effect: > On...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Run series of query -- I don't want any pop-up
I have generated 4 queries (1. empty all, 2. import data, 3. generate information, 4. Make a new table) I've created a button to run these 4 quries sequencially. ------------------------------------------------------------------------------------ Problem: MS ACCESS pops up "confirmation dialog" to ask me whether to delete/modify the table. ------------------------------------------------------------------------------------ Question: I don't want any question to be asked. I just want those queries to be executed once i press my button. -------------------------------...

Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location. The code looks like this: Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut Range("D5").Insert(xlShiftToRight) However, when it reaches this point, I get an error saying "Cut method of Range class failed". I've tried changing the destination range so that it reads similar to the origin range, but that didn't help. Can someone help me get the syntax on this line correct? I've got several similar lines further down in the code. If I can fix thi...