Is there a danger in switching the data type of a field in Access?

[kw127533]

I'm working with a very large Access database that has been maintained by my company for many years. There are nuisances that occur due to a field in one of the tables being coded as a text field, when I believe it should be a number field (this will stop the small nuisances from occurring). Is there any danger to the database if this field is changed? This table is a master table that is linked to many queries, forms and reports so I don't want to alter the data type for this field if it could potentially cause errors in any of the other queries, reports or forms down the line.

Is there a reason this field may have been coded as text instead of a number that I could be unaware of? The values in the affected field range from 0 to around 30,000 and are whole numbers only.

I realize this may be an obscure question and may not be easily answered, however, I'd appreciate any feedback.

[BikeToWork]

First back up the database and make a copy of the table. Next, check queries using the text field for joins. If you find none, create a new numeric field to hold the converted number value from your text field. If you do find joins based on the text field, they must be joined to another text field or there would be a data mistype error. This could turn out to be difficult, but you'll need to figure out how to join your numeric field to a numeric field in the other table of the query or else change the join field in the other table in the query to a numeric datatype. Next, check to make sure all values in your text field are numeric. Run a query where you use the IsNumeric() function to find any non-numeric values. Then, update your numeric field to CLNG(TextField) after you resolve all non-numeric values in the text field. Finally, rename your text field and rename your numeric field to the previous name of your text field. Good luck.

[zmbd]

BACK UP THE FILE!
I can not stess that enough.
Because we do not know the schema that your original file was developed under it is hard to say. However, most likely because the default field type is text your original developer simply used the default.

There most likely will need to be adjustments made to your queries. Because the field is text you will have quote marks around the search terms. These will have to be removed for numeric fields.

You will also have to look at table joins... all the other table fields that rely on that "text" form of the numeric will have to be delt with too... and that can be a bear.

There's really only one way to findout what will break...

Once you have a back up... make one more copy. This will be your development copy. So now you have, the production file, the backup file, and the production file.

If this is a split database (you do not indicate this) then make those same backups and copies for each file.

Now take your production copy, put it in an easy to access location on your PC/Network... re-link any linked tables if needed and check to see if it works correctly.

Now make your change.

Test the database, fix any issues, and retest. Once this works as expected you will need to figure out how to impliment them to your production copy.

For forms, reports, and queries you can copy those objects over. For the table you will need to do that change by hand.

let us know how you get a along.


BACK UP
BACK UP!
BACK UP!!!

[kw127533]

Is there a quick and easy way to check for joins? There are simply too many queries for me to check through them one by one.

[zmbd]

For Joins,
Open the file.
For ACC2007/newer>
Ribbon>Database Tools>Relations hips>Relationsh ips

For ACC2003, it's in the file menu, same thing.

This will open a window that should show any defined relationships. This however, has nothing to do with your queries.
To find queries that have the field in question TheSmileyCode has written a simple function to help out and can be found in our insights articles... the direct link http://bytes.com/topic/access/insigh...ontrols-string
Please take the time to read thru the article and the comments before using the code. Most questions about it should allready be answered therin.

[GKJR]

What are the nuisance errors you are getting? I would think your developer made that decision intentionally and you should work through the nuisance errors rather than change the data type. I have chosen to use text fields that hold number values for sorting reasons in some applications- text fields sort alphabetically instead of numerically. They will also hold leading zeros and you can concatenate their value to other string values directly. You may cause a problem that you don't spot right away somewhere down the line. I would suggest that you be absolutely sure you're not making a mistake before proceeding. You may cause yourself a major headache instead of a nuisance error.

[zmbd]

GKJR has some very valid points.

The warning if issues... hence my very first admonishment... back up and my second admonishment... use a development copy.

GKJR: Sorting: In this case, kw127533, doesn't mention having any alpha charactors in the field and BtW suggestion to check for only numerics will turn up this hazard; thus, the numeric vs alpha sorting is irrelevant unless you want a sort like 1,11,1111,2,22, 2222 instead of the expected numeric sort of 1,2,11,22,1111, 2222

Formating: Any issues with the formating and string functions are easily delt with requiring very little effort and IMHO rarely a reason to use a Text field for STRICKLY numeric data.

[kw127533]

I think you're right, GKJR. I don't think I'm going to mess with this since the database in question is just too important to too many people and is used on a daily basis.

I think this one is over my head. I'm glad I asked before making a potentially huge mistake. Thanks all for your quick responses!!

[kw127533]

One more question...Is there a way to change a field's data type through a query? This would allow me to alter the data without affecting other parts of the database.

[twinnyfo]

kw,

Once you pull a field into a query, you can manipulate the data however you want in an output field. Remember, you are only extracting data with a select query.

You are not really "changing the data type," but you are changing how the data may be displayed. I hope this makes sense.

For example you can pull the text value "20040814", which may look like all numbers--but it is text.

When you grab that text value into your Query, you can change it all around to make it useful:

=Format(CDate(CInt(Left([TextValue],4)) & "-" & _
      CInt(Mid([TextValue],5,2)) & "-" & _
      CInt(Right([TextValue],2))),"dd mmm yyyy")

Would render: 14 Aug 2004

However, your data type is still text in the underlying table.

[kw127533]

Ok, great. Thanks! At least I can fix the errors as they occur in certain objects rather than jeopardize the whole database's functionality. This will do for now. I appreciate everyone's help!!