Change a Field's Data Type

When you first design and build a database, you plan one or more tables, define the fields (columns) for each table and set a data type for each field. For example, if you need to store dates and times, you set a field to the Date/Time data type. If you need to store names and addresses, you set one or more fields to the Text data type, and so on. Here are some steps on how to change a field's data type in Microsoft Access 2003.

Sponsored Links:

Related Topics

Change field type
I have a table with 260,000 records. I need to change a field type from character to date. When I try to change a field type from text to date I get the following error:

Microsoft Office Access can't change the data type.
There isn't enough disk space or memory.

What causes this error and what can I do to fix it?

Memo to Text Data Type
I have a couple of fields spread across the database named "Notes" which is currently set as memo data type, I would like to change it to text data type.

What I already know:
1. Text data type can only have a maximum 255 characters.
2. Memo data type can store up to 1000 characters.

* I am sure that we don't need more than 255 characters for it; not now not ever, and we currently don't even have 50 characters stored in a single field so the truncation matter is out of the question.

What I would like to know:
1. Could I just directly change the Data Type of All Notes field in our Back end from MEMO to TEXT. Is it safe that way?

2. Is there a possibility of a corruption issue after the change or future use?

Modify or change the data type setting for a field
This article explains how to set or change the data type applied to a table field in a Microsoft Office Access 2007 database. When you first design and build a database, you plan one or more tables, you plan the fields (columns) for each table, and you set a data type for each field. For example, if you need to store dates and times, you set a field to the Date/Time data type. If you need to store names and addresses, you set one or more fields to the Text data type, and so on.

Change field to combo box w/out data loss
Using access 2003 is it possible to change the type of a field from text to combo/picklist without data loss? Whenever I use the Lookup wizard it warns me that all the data will be lost when I try to change it.

I have a field in which the user is typing the same 4 values over again so it makes sense to change the field to a picklist. There are 400 records so I'd rather avoid having to go through each record and re enter the data.

can't change the data type or field size
I am creating/editing updating fields in table design. I am trying to change the field size of the first field. But getting message. 'You can't change the data type or field size of this field, it is part of on or more relationships. I deleted the relationship and still get the message. Can I screw anything up if I delete any other relationships. Or does access re-connect all relationships on demand.

Change Access 2010 Table Field’s Data Type
In Access 2010, you will a lot of Table Tools to let user change table fields settings. Since data type has to be specified for each field, it also allows user to change the data type on the fly without opening database in design mode.

Change Field Type for Linked Table FE/BE
Now I know you cant directly change the data-type for the BE table from the FE sections.

However is it possible to use the FE to open the BE DB, access the BE Table with the field type I want to change, and then change it? Even if I were to have some code in the BE that I would send data to or something.

Would something like this be possible?

OpenDatabase ("C:\AccessDatabase_be.mdb")

(WhateverThingToPointToBEDatabase).Execute "ALTER TABLE [" & strDatabase_Table_ID & "] ALTER [" & strField_ID & "] [" & strField_Type & "];"

Data Type mismatch in criteria expression
I have an update query that is giving me the error message in the thread title. I know what field the error is occuring in. The field in the table to be updated has a "text" data type. The criteria I'm using for the field in the update query is as follows: Like "2011A*", becasue I only want to update different types of 2011 Actual data in that field.

I don't want to change the data type for the field in the base table because it is linked to to many other tables and queries in the database and I fear that messing with it will screw up abunch of other relationships

Can't change Table Field Name to All Caps

I have a table with a field name "distcode." I need to change it simply to all caps; DISTCODE. In Design Mode I type DISTCODE, save, and when I view the table the field name is all caps. But when I close the database and reopen the table the field name is back to all small letters. What is forcing this default change?

The Data Type is Text, and this table is not linked to another table

Type mismatch in query
I have two tables that I need to query. Table 1 has the field stored as a data type is a Number (Long Integer). Table 2 has the field stored as a data type of Text.

They are essentially the same field in both tables, two different developers created the data types. I don't have the option to change one of the data types at this time. Is there an easy way that I can run a simple query over these two tables using the mismatched fields as the join?

Data type mismatch error
The database that applies the following code has had a recent change:


The change was formatting the [ACTION] field from an number to a text format. Doing so would allow an alphanumeric input in this field on a Form.

This change has resulted in the following error:

"Run-time error '3464': Data type mismatch in criteria expression."

Change Format of “Number” Data Type
Access 2010 also allows user to change format of all supported data types. This post covers changing format of Number data type. To change the format of the field having number data type, open the table in Design view, right click the table and click Design View.

Relationship Problem
There are about 5000 entries now and I don't relish the idea of re-doing the whole thing. Here goes.

I have a db that I need to correct to ensure my tables will work with each other. Table 1 has Field Name [ServiceType] - Data Type [Text], Table 2 has Field Name [ServiceType] - Data Type [Number].

These two fields are the same data but are entered from two different forms that perform two entirely different jobs.

I want to change table 2 Data Type to "Text". I get an immediate error message that says I can't do this because there are relationships between the two tables and I need to delete this or these relationships before I can make the change.

When I click on the "Tools - Relationship" tab I get a screen that indeed has the relationship. I delete the line between the two tables close and save the change.

Then I try to make my change and the error message returns. I checked the relationship screen again and find that the change I made is ok.

What am I missing? Is there another path to the relationship screen I should be looking for such that I can make the relationship changes and therebye make the table changes?

Importing Data when some of the fields are blank
I am trying to import a date field that is sometimes blank into a table. If I leave the Data Type as text all is well. However, I want to provide the users the ability to search date ranges using this particular field.

When I change the Data Type to Date/Time format it brings in all of the data fine until it reaches a field that is blank and then stops. I have required set to No and I am at a loss as to what to do next.

I have update excel file to access table. one of type of table field is text in access table. (because excel field was text). but there is numeric values in text field like -2,345.67 . inorder to sum or to make another mathematichs operrations, I have to turn field type to numeric type. but if I change field type, all numeric data in field does broken. how can I solve that problem.

waiting your help I m sending my best

Changing datatype in table
My database consists of one large table of approx 3.9m rows and about 12 different columns. One of my columns is of a data type "date/time" but i need the format of this column to be "text"

The problem is that when I right click the table/go to design view/ and try to change the data type, I get the error "Microsoft Access can't change the data type. There isn't enough disk space or memory."

Which means i can't take the easy way out. Is there a way I can just create a new identical table and change the data type of my one column? I was thinking there might be something in SQL I could do, or some how in creating an identical table where only the field type has changed. I should note, I looked up this using the access in file help, and it appears the ALTER TABLE SQL will not work on my machine.

I'm really at a loss, because the dataset was created from a txt file which as been lost to the ether, and I can't import the database into excel using a macro I didn't write unless the format of my column is text.

How to trap and change the default data type validation error?
I've been teaching myself Access programming and am new to this forum. I have looked here and elsewhere for a problem but have not found a solution, hoping someone here can help.

In a form control, I am doing a number of data checks and generating appropriate error messages, using VB in the "on exit" event. The field I am checking is declared as data type number in the underlying table. If the user mistakenly enters text, Access generates an error "The value you entered isn't valid for this field." I want to change that default error message. I have learned how to change other default error messages, but I can't figure out how to trap and change this one. Note -- I don't want to check for the error in the form, because there are a dozen or so controls in the form where I need to do this check

Database Strings
A string is text made of a readable letter, a symbol, a word or a group of words. When creating a table in Datasheet View, to apply a Text field:
(1)If you create a new field by replacing the Add New Field column header with a name, the new field automatically receives the Text data type
(2)To change the data type of a field, click its column header or a cell under its column. In the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Text
Learning Objective: String-Based Data Types, Operations on Strings, String Concatenation, Document and Object Linking, String-Based Data Types: The Hyperlink, Objects and Data Fields, An Attachment, Using OLE Objects.

Change Percentage Data Type
I use Access 2003

Ive set the Data Type on a field name to = Percentage
Ive tried to use all - Field size types

I want to enter in 50 in the table and have the output be 50%
currently it goes to 5000%

Is there anything I have to add in the Validation rule to make it show the exact value?

Could some one kindly tell me a solution as i've tried a few methods none seem to work

How to Decide Which Data Type to Use in a Table Field
The default data type for a Microsoft Access field is text, but you can specify other data types if you wish. Choosing a specific data type ensures that you will get the best results from your queries and searches. Here are some steps on how to decide which data type to use in a table field in Access 2003.