Union Queries - Results showing as text & not numbers

To cut a long story short, i've got a union query that is a full outer join 
of 2 tables.

Anyway, the results i have got back needed formatting to no decimal places.

How do i do that?

I've tried leaving it as it is and formatting it in the report, but it still 
doesn't format and i can't total it up.

It's like it is showing as a text and not number.

Is there a way of converting the result to a number, particularly within a sql

0
Utf
12/14/2007 9:09:01 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
2297 Views

Similar Articles

[PageSpeed] 54

Using the Format() function has the effect of turning the result into text.

Could you use the Round() function instead?

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Clive" <Clive@discussions.microsoft.com> wrote in message
news:853C7696-C463-4856-9342-7FA3C23CC09B@microsoft.com...
> To cut a long story short, i've got a union query that is a full outer 
> join
> of 2 tables.
>
> Anyway, the results i have got back needed formatting to no decimal 
> places.
>
> How do i do that?
>
> I've tried leaving it as it is and formatting it in the report, but it 
> still
> doesn't format and i can't total it up.
>
> It's like it is showing as a text and not number.
>
> Is there a way of converting the result to a number, particularly within a 
> sql 

0
Allen
12/14/2007 9:41:43 AM
Thanks for your reply

I've tried using these. It appears to change the look of the number, but it 
still treats it like a text field and not numeric.

"Allen Browne" wrote:

> Using the Format() function has the effect of turning the result into text.
> 
> Could you use the Round() function instead?
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Clive" <Clive@discussions.microsoft.com> wrote in message
> news:853C7696-C463-4856-9342-7FA3C23CC09B@microsoft.com...
> > To cut a long story short, i've got a union query that is a full outer 
> > join
> > of 2 tables.
> >
> > Anyway, the results i have got back needed formatting to no decimal 
> > places.
> >
> > How do i do that?
> >
> > I've tried leaving it as it is and formatting it in the report, but it 
> > still
> > doesn't format and i can't total it up.
> >
> > It's like it is showing as a text and not number.
> >
> > Is there a way of converting the result to a number, particularly within a 
> > sql 
> 
> 
0
Utf
12/14/2007 9:48:02 AM
When you view the column, do the values left-align (like text) or 
right-align (like numbers)?

Any function that returns a variant - such as Nz() - also has the effect of 
causing JET to treat the result as text.

If necessary, typecast the value with CDbl() or CLng() or whatever. 
Unfortunately, these functions can't handle Null, so you may need to use 
Nz() inside them.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Clive" <Clive@discussions.microsoft.com> wrote in message
news:4E17762E-566F-40DF-B7A8-7899615779C2@microsoft.com...
> Thanks for your reply
>
> I've tried using these. It appears to change the look of the number, but 
> it
> still treats it like a text field and not numeric.
>
> "Allen Browne" wrote:
>
>> Using the Format() function has the effect of turning the result into 
>> text.
>>
>> Could you use the Round() function instead?
>>
>> "Clive" <Clive@discussions.microsoft.com> wrote in message
>> news:853C7696-C463-4856-9342-7FA3C23CC09B@microsoft.com...
>> > To cut a long story short, i've got a union query that is a full outer
>> > join
>> > of 2 tables.
>> >
>> > Anyway, the results i have got back needed formatting to no decimal
>> > places.
>> >
>> > How do i do that?
>> >
>> > I've tried leaving it as it is and formatting it in the report, but it
>> > still
>> > doesn't format and i can't total it up.
>> >
>> > It's like it is showing as a text and not number.
>> >
>> > Is there a way of converting the result to a number, particularly 
>> > within a
>> > sql 

0
Allen
12/14/2007 11:14:13 AM
Thank you Allen, this has helped a lot

"Allen Browne" wrote:

> When you view the column, do the values left-align (like text) or 
> right-align (like numbers)?
> 
> Any function that returns a variant - such as Nz() - also has the effect of 
> causing JET to treat the result as text.
> 
> If necessary, typecast the value with CDbl() or CLng() or whatever. 
> Unfortunately, these functions can't handle Null, so you may need to use 
> Nz() inside them.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Clive" <Clive@discussions.microsoft.com> wrote in message
> news:4E17762E-566F-40DF-B7A8-7899615779C2@microsoft.com...
> > Thanks for your reply
> >
> > I've tried using these. It appears to change the look of the number, but 
> > it
> > still treats it like a text field and not numeric.
> >
> > "Allen Browne" wrote:
> >
> >> Using the Format() function has the effect of turning the result into 
> >> text.
> >>
> >> Could you use the Round() function instead?
> >>
> >> "Clive" <Clive@discussions.microsoft.com> wrote in message
> >> news:853C7696-C463-4856-9342-7FA3C23CC09B@microsoft.com...
> >> > To cut a long story short, i've got a union query that is a full outer
> >> > join
> >> > of 2 tables.
> >> >
> >> > Anyway, the results i have got back needed formatting to no decimal
> >> > places.
> >> >
> >> > How do i do that?
> >> >
> >> > I've tried leaving it as it is and formatting it in the report, but it
> >> > still
> >> > doesn't format and i can't total it up.
> >> >
> >> > It's like it is showing as a text and not number.
> >> >
> >> > Is there a way of converting the result to a number, particularly 
> >> > within a
> >> > sql 
> 
> 
0
Utf
12/14/2007 11:50:01 AM
Reply:

Similar Artilces:

Show Excel in two separate instances/two monitors?
I've recently been upgraded to two monitors (one of the few things IT has done right around here!). But try as I might, I can't get two Excel (2003) workbooks to show in two separate instances so I can put one on each monitor! Any solutions? Ed Hi Ed, open 2 workbooks -- click the Maximize button (so you are in resize mode) --- drag a workbook by the titlebar to the second monitor... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Ed wrote: > I've recently been upgraded to t...

formula tu sum up to a number till 7
This is the data I have: 2 3 4 5 5 6 6 7 7 1 4 5 1 2 I want in column C to G cells to be filled till 7 starting to count from B and then starting again. Result I want: A B C D E F G 2 3 4 5 6 7 1 4 5 5 6 6 7 7 1 4 5 1 2 And so on... How can I do this... Thank you so much!!! I don't fully understand this. Why do you not show 2 as the starting value in column C? Why do you have a 1 after the 7? Is this the corresponding value from column B? Would C2 start with 4 and continue across until you next...

How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I want my members to report their wins - 1st, 2nd, and 3rd place - in tournaments to my web site . I would like them to imput: their nickname, date of tournament, tournament Room #, tournament points played for, and host of the tournament. I would further like the calculation to display any change in Ranking as a text cell. Additionally, I want to show for the member, how many more points are needed to get to the next levle and what that (Text)level is. If there is a "what if" for this please advise. Ri...

Formula argument based on text
Hi All, I have cells (A1:A10) populated with text ("Pass" or "Fail"). In cell (A11) I want to have a formula that returns the text: "pass" if all the cells (A1:A10) contain the text "pass" "fail" if any one of the cells (A1:A10) contains the text "fail". How would I write the formula to achieve this? Regards gregork In A11 enter: =IF(COUNTIF(A1:A10,"Fail")>0,"Fail","Pass") HTH "gregork" <gregork@paradise.net.nz> wrote in message news:0RXSb.19911$ws.2664893@news02.tsnz.net... > ...

new emails
When I start a new email the text looks very small and hard to read but when I look at it in my sent folder it looks fine.... why? Have you checked your zoom level already? See http://www.msoutlook.info/question/95 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Mike Bunting" <Mike Bunting@discussions.microsoft.com> wrote in message news:228BBE80-21D2-4ADF-9048-CFB6C9DA3E9...

query-based distribution group not showing in Outlook
Software: Exchange 2003 standard and Outlook 2003 I have created a query-based distribution group and it isn't showing up in Outlook Address Book. If I create Distribution groups they show up in the Address Book. How do I get the query based groups to show in the Address Book? Under Exchange Advanced I have confirmed that the check box to hide list is unchecked. Thanks, Colin M. On Tue, 27 Dec 2005 10:54:52 -0800, "Colin" <legendsfan@nospam.nospam> wrote: >Software: Exchange 2003 standard and Outlook 2003 > >I have created a query-based distribution ...

option to show or hide social security number throughout system
Now that identify theft is a big issue, a great feature in HR and/or UPR would be an option to hide or show SS# on windows and reports. I'm having to cusomize HR and Payroll reports just to remove the SS#. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://ww...

ALTER TABLE statements showing up in my stored procedures
Hi, Recently, I noticed some ALTER TABLE statements in my stored procedures. I have no idea where they came from. The only thing I can think of is that I do generate scripts to keep my local copy of the database where I develop my application and the production server in sync. I think these statements started showing up after I upgraded to SQL Server 2008 R2 (full version on production server and Express on the local machine). I just tried removing them from one of my stored procedures and they came right back. I then deleted the stored procedure and recreated it without t...

Check Number format
In Great Plains our checks were initially set up with 20 zeros. For some reason when running the Vendor check request report on some of the PC’s (on screen and to the printer) the entire check number does not show up so it was requested that we remove 8 of the leading zeros. I went into Cards>>Financial>>Checkbook and pulled up the appropriate checkbook and removed the leading zeros from Next Check Number and Next Deposit. When I print the report, the zeros are still visible. Am I changing the correct field or will this change not affect existing numbers in the report I...

Move gridlines in a chart so that they are above the numbers
I have done a chart in excel and the gridlines are set so that the y axis numbers are inbetween the gridlines. When I choose minor gridlines I get double the gridlines. I just want a gridline above each number in my y axis Double click the axis that intersects the gridlines, and on the Scale tab, uncheck Axis Crosses Between Categories. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tara Medway" <Tara Medway@discussions.microsoft.com> wrote in message news:47400C54-74B4-4E67-93AD-AE8B3F09BE47@microsoft.com...

shortcut ket to change formatting of text
hey, whats the shortcut key to change numbers to dates, and vice versa. cheers : -- Message posted from http://www.ExcelForum.com Hi! This may be the sort of thing you want: CTRL+SHIFT+# Apply the Date format with the day, month, and year. This is a quote from Excel Help ( use "Keyboard shortcuts" for th search) Al -- Message posted from http://www.ExcelForum.com ctrl-1 will pop up the Format|cells dialog ctrl-~ (ctrl-tilde, aka ctrl-shift-backquote on my USA Keyboard) will format a cell as General ctrl-# (ctrl-shift-4) will format as d-mmm-yy Try searching excel's...

Auto Number
Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In the bound form, frmProvider, I would like txtProcessProviderNumber to increment by 1 on each new record each new record entered during the Year. The year will be entered manually by the user via combo box in text field. Once the Process year changes to a new year I would want the numbers to start all over at 1. How can I do this. Any help would be greatly appreciated. -- Gary i n Michigan, USA GaryS wrote: >Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In >the bound form,...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

sum numbers and ignore text
Hi there, I need some help please. I have a row of numbers and text and I need to sum the numbers only. How to do? a1 a2 a3 a4 a5 1 dnr 3 4 dnr total 8 thanks in anticipation ditchy Ditchy, a simple SUM formula will do it, since the SUM worksheet function ignores text values. So in your example, it's =SUM(A1:A5). -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "ditchy" <ditchy@ncable.net.au> wrote in message news:dda1ad29.0405221934.5787f60f@posting.google.com... > Hi there, I need some help please. I have a row...

imported distributions lists show as contacts
Client exported his Contacts containing several Distribution Lists, then imported into a new system. Teh imported DL's show up as separate Contact Lists...how, why, and more importantly, is there a simplified way to correct? Version of Outlook they were exported from and imported into? How did you import them? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

Line Numbering
Can I add line numbers to an excel spreadsheet? If so, how? Thanks "Ernie" <edeleon68@hotmail.com> wrote in message news:126f501c3f6ef$e59575e0$a301280a@phx.gbl... > Can I add line numbers to an excel spreadsheet? If so, > how? > Thanks Use the formula =ROW() copied down. Hi do you neet the row numbers on your printout? If yes goto 'File - Pagesetup - Table' and check the option to inlcude row and column headings -- Regards Frank Kabel Frankfurt, Germany Ernie wrote: > Can I add line numbers to an excel spreadsheet? If so, > how? > Thanks ...

Query Question... 03-06-10
I have this expression to get all data before "50 underscores" Expr1: Left([Job_Operation.Note_Text],InStr(1,[Job_Operation.Note_text],"_________________________________________________",1)-2) & "" & DLookUp("[Note_Text]","Operation","[Operation]='H.T. 01'") ie: First Line "Blah" 'CR' Second Line ___________ 'CR' Third Line "Blah" The text after the underline is getting replaced by 'H.T. 01'. I seem to be getting additional Chariage Returns left behind. I h...

Query related to
Hi In yy MFC based SDI multithreaded application i am getting the following error: Debug Assertion Failed! File:dbgheap.c Line:1044 Expression:__CrtIsValidHeapPointer(pUserData) Now i dont see a reason that y am i getting this error. The scenario is as follows: Step 1 --> I execute my SDI App (main thread). Step 2 --> I start some process in another UI thread. After running it for some time i stop it by pressing the Stop toolbar button. In the toolbar handler function, this UI thread is being successfully destroyed. Now i again start some process in my main thread. In the function t...

Typing text is impossibly slow in text boxes when in publishing layout
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I try to type or edit text in a text box in a template document (a newsletter template) the typing, editing, deleting, backspacing or what ever, is absolutely impossibly slow. Am I doing something wrong or do I have something set up wrong. If it remains this way I will not be able to work in this program. Apart from using Publishing Layout View, I suspect you are not doing anything wrong. Publishing Layout View requires very powerful hardware, and even then it tends to crash a lot. If I were you...

M2006 shows downloaded statement, but where is it?
For two accounts, the "View Downloaded Statements" shows that something was downloaded with 4-5 transactions each. However, the account register shows no such entries for me to accept etc. What gives? -- Aloke ---- to reply by e-mail remove 123 and change invalid to com Run the Quick File Repair and let's see if that fixes the problem. -- "Aloke Prasad" <aprasad123@columbus.rr.invalid> wrote in message news:etLKp15oFHA.3408@tk2msftngp13.phx.gbl... > For two accounts, the "View Downloaded Statements" shows that something > was download...

Queries not working after compiling the DB
I have built a Database and spilitted into FE & BE. in the FE i got 2 forms that are based on queries and used for searching. I made MDE file and used startup option to remove admin options so noone can change the DB. Now when i copy the MDE file on different PCs on network to access the BE on server I have noticed that Search option that are based on the queries is not working. Any Idea? while the same file is working fine on my computer. I have made sure everything else is fine. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1...

selecting text in a document
Hi All, I have a macro that seeks out a particular string of text in a document and then moves down one line and selects the remaining text nin the document and writes it to a variable and then uses it subsequently in the message section of an email. Recently this method has been failing because the text is not being selected. I don't know if the PC was in EST mode a the time but I need the macro to work no matter what. The code I use is as follows Selection.HomeKey wdStory Selection.Find.Execute "Issued at" Selection.MoveDown wdLine...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

text import wizard #4
I'm migrating a huge database out of our current business software int new software. I'm exporting into an Excel file so I can adjust inf before mapping to the new software. I've got the import wizard figured out but I'd like to *use it within column* of info and not just to import a whole file. are the functionalities of the wizard available within a column -- Message posted from http://www.ExcelForum.com Many of the capabilities are available if you choose the Data/Text to Columns... command what exactly are you looking for? In article <philtyler.1a7i9n@excelforum...

How do I use alpha page numbers (e.g., B-3a, B-3b, B-3c, etc.) in.
I am using Excel 2003 and need to use incrementing alpha page numbers (e.g., B-3a, B-3b, B-3c, etc.). Excel 2003 help only refers to numeric page numbers. Is there a way to format the page numbers to use incrementing alpha page numbers? I was hoping to see a better reply to your question, but a day has gone by so here is my $0.02. If you must, you can always similate the footer in the worksheet, just have to be consistent with row heights, etc. This might be more trouble than it's worth, but if want to try here's a formula that might help. =IF(QUOTIENT(COLUMN()-1,26)=0,"...