Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2014, 07:53 AM   #1
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Converting text to numbers

Hi All,
I have been scanning the postings and could find things similar to my problem but not exactly the same issue. Here is the problem. I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion? Help please.

exaccess is offline   Reply With Quote
Old 09-19-2014, 08:48 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,081
Thanks: 476
Thanked 893 Times in 848 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Have you tried changing the field from text to numeretic?
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 09-19-2014, 11:28 PM   #3
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Re: Converting text to numbers

Yes I have tried that. But I am still getting 21.6 instead of 21.60 or 59.8 instead of 59.80. I am probably missing an obvious point but what. Also I have another related question which is giving the final result of the program. I shall export these numbers to a spreadsheet. How can I set the formatting of the cells of the spreadsheet so that I get exactly the display I want.

exaccess is offline   Reply With Quote
Old 09-20-2014, 12:10 AM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,673
Thanks: 2
Thanked 2,059 Times in 2,014 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Converting text to numbers

Open the table in design view:
Set the Format to Fixed and Decimal Places to what you want.
Attached Images
File Type: jpg FixedNumber.jpg (21.6 KB, 344 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 09-20-2014, 06:48 AM   #5
DrPat
Newly Registered User
 
Join Date: Feb 2011
Location: Huntington, NY
Posts: 39
Thanks: 10
Thanked 0 Times in 0 Posts
DrPat is on a distinguished road
Re: Converting text to numbers

Hi Exaccess,

This can be a very useful tool as a public function.

I'm at work and don't have time to fiddle with this, but it seems you need to:
1. Find the number of characters after the decimal in your string
2. Coerce the string with CDbl() while setting the format to match the number of decimal places discovered in step 1.

to get you started, I found a public function to find the number of decimal places here.

Good luck,
/Pat

DrPat is offline   Reply With Quote
Reply

Tags
converting , double , numbers , text

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting numbers into text howlettb Reports 9 08-15-2012 12:30 PM
Converting text dates to numbers 12345678 Queries 3 10-09-2010 07:20 AM
Converting Text To Numbers paulS30berks General 1 06-14-2006 07:22 AM
Converting Numbers to Text danielgraham Reports 4 05-14-2004 10:57 AM
Converting numbers to text dwhite Reports 0 10-18-2002 01:45 PM




All times are GMT -8. The time now is 09:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World