CONVERT Numbers-As-Text to PURE Numbers when converting to xls ??

I have a table that has a text field which stores only numbers.  I'm not sure
why it is formatted as text...but it always is...and I currently have to
control over its creation.


I have a report based on a query which is very often "Output To" an XLS
format via a Macro (it very cooly works with one click) so it can be used in
a spreadsheet.

Well...that text field with numbers comes into excel as text, of course.   At
least the excel indicates so with little triangles in the cells' corners.
Excel also provides a little pull down mention which allows a quick convert
to true numbers.

BUT....it would be GREAT to be able to do this on-the-fly when it converts to
Excel via that Macro.  This would make a huge difference.

Does anyone know if this is possible (e.g. a setting in the output-to feature,
etc) ?

Thanks

-- 
Message posted via http://www.accessmonster.com
0
kev100
2/28/2006 5:38:41 AM
access.conversion 3037 articles. 0 followers. Follow

5 Replies
905 Views

Similar Articles

[PageSpeed] 14

Create a query that uses a conversion function in a calculated field,
and export the query instead of the table. E.g.

	NewField: CDbl([ExistingField])

On Tue, 28 Feb 2006 05:38:41 GMT, "kev100 via AccessMonster.com"
<u16246@uwe> wrote:

> 
>I have a table that has a text field which stores only numbers.  I'm not sure
>why it is formatted as text...but it always is...and I currently have to
>control over its creation.
>
>
>I have a report based on a query which is very often "Output To" an XLS
>format via a Macro (it very cooly works with one click) so it can be used in
>a spreadsheet.
>
>Well...that text field with numbers comes into excel as text, of course.   At
>least the excel indicates so with little triangles in the cells' corners.
>Excel also provides a little pull down mention which allows a quick convert
>to true numbers.
>
>BUT....it would be GREAT to be able to do this on-the-fly when it converts to
>Excel via that Macro.  This would make a huge difference.
>
>Does anyone know if this is possible (e.g. a setting in the output-to feature,
>etc) ?
>
>Thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

0
John
2/28/2006 6:21:00 AM
>Create a query that uses a conversion function in a calculated field,
>and export the query instead of the table. E.g.
>
>	NewField: CDbl([ExistingField])
>


John,

Thanks very much for that tip.  Sorry for not responding sooner.

I'm kinda new to Access and am learning bit by bit.  I mainly use the wizards
and pull-down functions...but several folks here have help greatly with SQL
scripts.

My table has a field in it contain numbers....use 1-300 or so.  The field,
the creation of which is beyond my control, is a 4 character text field....
for some unknown reason.

The field name is "route" the table name is "addrdata"

I have a query which references that field, and a few others.

That field is the only one which need to be converted to a number.  I have a
Macro which runs that query with the "output to to excel format" option.

I am not sure how to use "NewField: CDbl([ExistingField])" when creating the
query.  Does that line go in of the the fields in Design View of the query....
or is it entered in the SQL view?

and....would the command/instruction be:   CDbl (.addrdata.route)  ?

Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-conversion/200603/1
0
kev100
3/6/2006 4:16:37 AM
>I am not sure how to use "NewField: CDbl([ExistingField])" when creating the
>query.  Does that line go in of the the fields in Design View of the query....
>or is it entered in the SQL view?
>
>and....would the command/instruction be:   CDbl (.addrdata.route)  ?
>

Please forgive my too-quick reply.

I just took a moment to really look over the Design View layout and saw what
you were referring to.

It seems to work great.  I'll give it a try tomorrow on a few of the existing
query apps.

Thanks VERY much...this will make a big difference.  We'll actually be able
to sort now and have 2 follow 1 (rather than 10)  :)

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-conversion/200603/1
0
kev100
3/6/2006 4:27:47 AM
kev100 via AccessMonster.com <u16246@uwe> wrote in message
news:5c8a3709cf5f4@uwe...
> I have a table that has a text field which stores only numbers.  I'm not
sure
> why it is formatted as text...but it always is...and I currently have to
> control over its creation.
>
>
> I have a report based on a query which is very often "Output To" an XLS
> format via a Macro (it very cooly works with one click) so it can be used
in
> a spreadsheet.
>
> Well...that text field with numbers comes into excel as text, of course.
At
> least the excel indicates so with little triangles in the cells' corners.
> Excel also provides a little pull down mention which allows a quick
convert
> to true numbers.
>
> BUT....it would be GREAT to be able to do this on-the-fly when it converts
to
> Excel via that Macro.  This would make a huge difference.
>
> Does anyone know if this is possible (e.g. a setting in the output-to
feature,
> etc) ?
>
> Thanks
>
> --
> Message posted via http://www.accessmonster.com


0
steven
4/20/2006 3:31:18 AM
Hi Steven,

Try creating a query that uses a calculated field to convert the
"numbers-as-text" to a numeric data type. E.g.

	NumNumber: CDbl([TextNumber])

Then export the query.

On Thu, 20 Apr 2006 11:31:18 +0800, "steven" <lic_steven@163.com> wrote:

>
>kev100 via AccessMonster.com <u16246@uwe> wrote in message
>news:5c8a3709cf5f4@uwe...
>> I have a table that has a text field which stores only numbers.  I'm not
>sure
>> why it is formatted as text...but it always is...and I currently have to
>> control over its creation.
>>
>>
>> I have a report based on a query which is very often "Output To" an XLS
>> format via a Macro (it very cooly works with one click) so it can be used
>in
>> a spreadsheet.
>>
>> Well...that text field with numbers comes into excel as text, of course.
>At
>> least the excel indicates so with little triangles in the cells' corners.
>> Excel also provides a little pull down mention which allows a quick
>convert
>> to true numbers.
>>
>> BUT....it would be GREAT to be able to do this on-the-fly when it converts
>to
>> Excel via that Macro.  This would make a huge difference.
>>
>> Does anyone know if this is possible (e.g. a setting in the output-to
>feature,
>> etc) ?
>>
>> Thanks
>>
>> --
>> Message posted via http://www.accessmonster.com
>
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

0
John
4/20/2006 5:25:13 AM
Reply:

Similar Artilces:

Adding floating point numbers
hi the value 0.1 in the computer memory is not equal 0.1 so if u add it 10 times the result is diffrent then 1.0: double value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result<1.0 BUT: result=value*10; //result==1.0 WHY??? another example: float value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result==1.0 WHY??? can anyone explain it? thx On Thu, 28 Jun 2007 10:18:05 -0700, rsobies <rsobies@discussions.microsoft.com> wrote: >hi > >the value 0.1 in the computer memory is not equal 0.1...

A2K-TOOLS
Hi, i want to create a form to convert several hundreds of A97 dbs to A2K. does anybody know what which routines are behind the Convert to current Database-Version in the A2K-Tools Database Utilities option. How could this be integrated in my form for the convert-loop over the a97-dbs. many thanks f=FCr helping The vast, vast majority of Access 97 databases can be opened in Access 2000 (or any later version) and saved in Access 2000 format. No, I don't have a suggestion as to how you would automate that task. There are, however, some very accomplished Access developers...

Getting rid of "fax" numbers when sending e-mail
I use Outlook XP for e-mail and also for contacts. I use my "Contacts" folder as my address book. Whenever I send an e-mail, Outlook always asks me to choose which address I want for the contact, and it brings up both the e-mail address and the fax number. So I have to go through an extra step almost every time of choosing which I want. Is there any way to stop it from bringing up the "Fax" number? See http://www.slipstick.com/contacts/nofax.htm Lee Daum wrote: > I use Outlook XP for e-mail and also for contacts. I use my > "Contacts" folder as my add...

Printer does not print all text
For some reason one of my Publisher file is not printing all the text; only about 75% of the text is there. I have tried to print from a different computer but have the same problem. On screen, everything looks right and great. I am using Publisher 2007. Andy help would be appreciated. You might want to check the manufacturer's web site of your printer model for an upgraded driver. If you think your present driver is correct, uninstall it and then re-install. It is always wise to completely remove a printer driver before installing/reinstalling. How to clean up printer drivers...

Wrap Text for Merged Cells
Hi, Can somebody post some good VBA macro for wrapping text in case of merged cells (merged rows as well as merged colums or both merged rows and columns). Lot has been posted in this regard till now but i could'nt find any perfect solution to this. Please help. Mvrk If you've seen lots of code that has been posted, yet you don't say what makes that code "not perfect", how do you expect anyone to respond to your post? Should responders just guess what "perfect" means to you? Try posting back with more information - what you've tried, why it didn...

How do I move text to beside (rather than after) a photo?
I have MS Word 2007. When I place a photo or text box onto a page, I can't then get text to enter beside it. The cursor will only go to the side just above or below the photo. How do I enter text alongside the picture? Change the text wrapping on the photo or text box to Square. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Mrs Mainster" <Mrs Mainster@discussions.microsoft.com> wrote in message news:5C43DF85-E305-4DEC-8F8A-3BF1D544FF62@microsoft.com... >I have MS Word 2007. > When...

Outlook Express 6 change text format to HTML
All my incoming mail is plain text, and I've tried everything to my knowledge to convert my email to receive html format. All my emails usually come in HTML format from before. Does anyone know how to fix this on the OE6 version? Thanks. All is okay!!! I found the answer on another email and it worked fine. >-----Original Message----- >All my incoming mail is plain text, and I've tried >everything to my knowledge to convert my email to receive >html format. All my emails usually come in HTML format >from before. Does anyone know how to fix this on the OE...

Toolbar buttons (text and bitmaps) are disappearing, when using with MFC CToolBar class
Guys, help me to sovle the following problem I have a MFC SDI application (Vc++ 6.0) ( 2 Threads, UI and data acqusition thread). The UI is having a simple toolbar control with text and bitmap. The problem is once i run this application after doing some gui operation (like invoking some dialog box changing to diffrent view) the toolbar button is getting disappeared. sometimes the fonts of the toolbar button is getting changed or sometimes when i move the mouse over the toolbar, the buttons is slowly going away( only back ground color of rebar is comming. Is there any methods to debug this ki...

phone numbers
Hello: You know how you cannot integrate employees into Employee Maintenance in Integration Manager if the employee's phone number has parentheses and dashes in it? Well, I found a great TechKnowledge article that walks you through how to replace the parentheses and dashes and successfully integrate. But, if you have one employee in your integration file that does not have a phone number, then Integration Manager throws out the employee record altogether and says "Invalid use of Null: 'Replace'". So, I tried the code below. I added some lines to the TechKnowledg...

Should I convert from 97 to 03
I have an application created in access 97 that occationally gets errors that exit access. i.e. "... shut down by Windows. Sorry for the inconvenience." I can not figure out what is causing the error. I thought if I upgraded to 2003 that the error would stop. After reading the message boards it looks like versions after 97 are problem ridden. Since the expense of 2003 for 5 machines is a chunk on change and the potential for more problems appears to exist maybe the conversion is not a good idea. Any thoughts, ideas or input would be greatly appreciated. thank you If the 97 ...

How do i number a list of data in excel 2003?
I was shown once, but I can't remember how it was done. I have a list of 5000 things and I need to number then 1-5000, but don't want to go through all 5000. I have seen it done where you type the first few like 1-7 and then use a shortcut and drag it down the rest of the list and the numbers will populate. Please help! You can try something like this: =IF(B1="","",COUNTA($B$1:B1) copy down....or if there are no gabs in your list simply double click on the lower right corner of the cell. HTH JG -- pinmaster -------------------------------------------------...

Input from remote 10-key and laptop numbers very slow
The last few days I've experience a delay when inputting numbers into Excel 2000 worksheets. When I type in the numbers, they slowly enter themselves, one at a time pausing between each number. This is occurring both with the remote 10-key for the laptop and the number keys on the laptop. This is occuring in all files, no matter how big or small. I wouldn't say I have any overly complicated workbooks, mostly basic functions. I have had much bigger and more complicated before without a problem. When using the 10-key or the number keys anywhere else, Word, internet, e...

converting TO .docx
Is there a simple, free tool that will convert lots of Word files (in ..rtf format) to .docx? I've found some web-based ones but: I don't like the idea of submitting files to websites, whether I trust the websites or not. There are potentially several thousand of files to be converted, so a batch processing method is essential. Any ideas or recommendations? --=20 Bob http://www.kanyak.com A macro containing the following code will convert all of the files in the folder C:\Test Modify the line of code that contains that folder name to suit where you have the do...

Cell Number Format includeing other Cell Value
Hello, I needed to create some conditional number format - to say so... My goal: Cell A1 content is "m2" as I Enter in cell A2: "10" -> the output to cell A2 should be "10 m2". Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"... and so on.... Is that possible in Excel? (don't know VBA - yet :DD) Any help is appreciated! BR, Daniel How about just use a third cell: =a2 & " " & a1 daniel_of_vienna wrote: > > Hello, > > I needed to create some conditional number format - to say so... > M...

I have 5,00 images that need renamed to part numbers
I have 5,000 images I have to change to current name to a part # found on an excel spreadsheet. How do I change the actual name (not one at a time) but globally with these part #'s? On 2/1/10 4:01 PM, Elizabeth wrote: > I have 5,000 images I have to change to current name to a part # found on an > excel spreadsheet. How do I change the actual name (not one at a time) but > globally with these part #'s? This seems like something that you could do with VBA as long as there is a way to identify the new name from the old name (is the old name listed in the Excel ...

Error converting a lead to a contact
HI All We are getting an error whenever we try to convert a lead to a contact. From within CRM we get the following error. ----------------------------------------------------- License Error The selected user or user trying to log on has not been assigned a security role or license, and does not have sufficient privileges for this action. For more information, contact your system administrator. ----------------------------------------------------- Now I have checked out License Manager and this user is attributed to the license and I gave them membership of every role with all the privileg...

how do i convert an a4 poster into an a5 flyer
Please can anyone let a total MS Publisher beginner know how to convert an a4 poster into two a5 flyers on one A4 page? Many thanks. Setup your printer for A4, landscape. Page setup, custom, landscape, 14.9cm width, 21cm height. Publisher will say prints two copies per sheet. Copy/paste from your original poster and adjust the objects. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Jeremy" <Jeremy@discussions.microsoft.com> wrote in message news:90...

What is the formula to convert a whole column containing date to text?
What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

Convert QuickBooks time format to excel to calculate
When I export a Quickbooks "Time by Job Summary" report to Excel, the time is converted in a time and date format. If I want to put a dollar amount to that time, it needs to be converted into a number format. I can easily convert the time for anything less than 24 hours using =(D18-INT(D18))*24. How can I easily do that for any time greater than 24 hours? Give some examples of the type of data you want to convert. Are you trying to get the date into one column and the time (in Excel format) in another column, or are you saying that a time of say 5.5 days is showing up a...

Check value range, then return spesific number?
Hi. I want to implement a function in my spreadsheet, that gives me this: When I enter a number into a cell, another number shows in another cell, based upon the number first entered. A bit more spesific: The idea is to calculate the number of instructors needed for a week end seminar. 1 participant requires 1 instructor. Same for 2 participants, and 3. 4, 5 and 6 participants requires 2 instructors. 7, 8 and 9 participants requires 3 instructors. And so on. For every 3 participant, we apply 1 instructor. So, i.e. if I enter the number 5, i want the number 2 in the cell below. I trie...

Line Item Comment Text On Forms
Good morning. I am trying to get the line item comment text to appear on my Sales Transaction entry forms (i.e. quotes, orders, and invoices). At present, this field shows but only 4 lines of text and only so wide. I would like to get infinite lines of text with the field growing or shrinking as need be on the form. Would appreciate any advice here. cheap ugg > On Tuesday, July 26, 2011 10:50 AM Mark Plaideau wrote: > Good morning. > > > > I am trying to get the line item comment text to appear on my Sales Transaction entry forms (i.e. quotes, orders, and invoi...

euro converter
does anyone know of an add-in to convert euro to aud. the inbuilt converter converts everything to and from but to aud...... go figure!! This site http://finance.yahoo.com/currency?u shows current exchange rates. I don't know of an add-in, but they DIY method isn't difficult. ...

Converting Business Card in Publisher to V-Card in Outlook
How can I make this happen? thanks Jan Why not create it in Outlook? http://office.microsoft.com/en-us/templates/results.aspx?qu=electronic+card&sc=4&av=TPL -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jan Ferris" <Jan Ferris@discussions.microsoft.com> wrote in message news:EE01E5B0-FC65-4BF8-AE70-805B44ED6968@microsoft.com... > How can I make this happen? > thanks > Jan Since I am novice level, I can't figure out how to import artwork and place it exactly as I want it. "Mary Sau...

how to change numbers into words,
In excel how to change numberical data into english words, many times currency figures are required to be stated into english words as well so what is the formula for this problem, for example for US$ 5400 : $ Five thousand four hundred is the conversion into words. See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "Mukesh Dhoot" <Mukesh Dhoot @discussions.microsoft.com> wrote in message news:B59710A8-2377-4CF5-98B0-229933A3DE33@microsoft.com... > In excel how to change numberical data into e...

Disappearing text
Previously inserted text disappears and blinks on the screen, then disappears again. How and where do I find it? I would look for a video driver upgrade from the manufacturer's web site. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dismayed" <Dismayed@discussions.microsoft.com> wrote in message news:A9CBBF8E-CA4B-455A-B29F-869B03F796F6@microsoft.com... > Previously inserted text disappears and blinks on the screen, then disappears > again. How and where do I find it? ...