Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Access Macro to convert text fields to number

Hi,
I have a macro that imports some text files and spreadsheets to my database, however with one of the spreadsheets some of the fields are imported as text, but they should be numbers. I need to add a step to my macro that will convert these fields into number - I know its easy enough to do it in design view of the table in question - but I need it done automatically.
Thanks
0
belkingts
Asked:
belkingts
  • 4
  • 2
1 Solution
 
ducky801Commented:
You can write and Alter Table SQL statement then execute it with VBA.  See my example..
Dim str_SQL as string
str_SQL = "ALTER TABLE YOURTABLE ALTER COLUMN [YOURCOLUMN] TEXT(200)" 
 
docmd.runsql str_SQL

Open in new window

0
 
Jeffrey CoachmanCommented:
belkingts,

Or,
...Endevor to avoid the issue altogether.

Set up "Data Validation" in Excel to only allow for Numbers to be entered into a certain Column(s).

Excel 2003: Data--Validation
Excel 2007: Data-->Data Tools-->Data Validation

This way it "will" import into Access as a number, thus you can avoid the whole "convert" business altogether.

;-)

JeffCoachman
0
 
belkingtsAuthor Commented:
The problem with Data validation is that the excel file is automatically downloaded from Business Objects each morning, with the columns in question mostly appearing as numbers with the exception of zero's which come out as text. There are more text zero's then there are real numbers, so this is why I think Access is treating the columns as text.

My SQL coding skills are not strong enough to interpret the previous comment into a usable solution I'm afraid!  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
OK,

As far as the previous code,

You must create a form and drop a button in on the form.

The code goes on the click event of the button

JeffCoachman
0
 
Jeffrey CoachmanCommented:
My post that you accepted was not the solution.
It was simply a clarification of ducky801's post.

The points should go to him.

I have requested that this be changed

JeffCoachman
0
 
ducky801Commented:
Thanks Jeff C!
0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now