[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Modify Data type in Access Macro from text to number

Posted on 2007-10-10
Medium Priority
Last Modified: 2013-11-27
I have a process that I would like to automate.  The process is using an access table that has a field that was imported in as "text".  I would like to change the data type to number.  I can manually do it by just going into the the table.  What is the code to modify the data type FieldA from "text" to "number"?
Question by:morinia
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 20049187
currentdb.execute "alter table NameOftable alter column NameofField number"
LVL 44

Expert Comment

by:Leigh Purvis
ID: 20049258
That'll do it.  Though IMHO you should be pretty precise about the data type you want.
The above will give you a Double number type by default.  Which is OK if you want it - but
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA Double"
would be more explicit.
And of course if you wanted a long integer then you should be specific about that:
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA Integer"
An integer:
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA SmallInt"

and so on.
Same idea though - and for a single operation likely the best choice.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question