changing field format

[laurat]

When I originally designed a database, there was a field called Written By. The users told me to make this an alphanumeric field because the employees could put their name or employee num. Well now that the database is in use they decided they only want the employee clock number entered in that field so they want me to change it to a numeric field. I thought I could just go into the table and change the text to Number and it would apply to future data that was entered. However, when I did so it gave me a message that said errors were encounted and it would delete a couple hundred records (The records that have letters in that field). Since these records cannot be deleted is there a way to get around this or another way to go about it??
Thank you

 

[David R]

You'll have to convert those existing records to numbers

I'm assuming here that every employee has a clock number, and initially they could use either their name or their clock number.

The problem comes if they could put in their "name", versus an Alphanumeric ID. If it's their name, then did they enter it the same way each time? Or did James McCarthy enter himself as James A. McCarthy one day, James McCarthy another, and Jim McCarty (sic) a third day? You've got a real problem if that's the case, cause you'll have to go through all records which are alpha and convert them by hand. Hint: Try making a query field: CInt([Written By]) and finding those records that have Null value in this field. Those should be the ones with letters in them.

If there's an exact correspondence between what they put in and their clock number, then your problem is lessened. Do you have a table with their names (as entered) or IDs and the clock numbers? You can use an update query to input their clock number in those fields where the name/ID matches the value in the [Written By] field.

With a bit more information someone can help you with this nightmare project.

 

[Rakier]

Another thing to think about is the necessity to change it to a numeric field at all.

If you are not going to directly use the field for calculations (and I wouldn't think that an employee id would be used for that) than you can just leave it as a text field and allow the alpha numeric text.

However, that still won't change the previously existing data into numeric format, you would still have to do that as previously suggested.

 

[laurat]

It is not that anyone wants to do calculations with they field, they just want the data entered to all be the same. They now want only the clock number to be entered. Instead of changing the field to numeric, is there a way I can place criteria on that field that will only allow numbers to be entered from now on??

 

[Rakier]

Check in the table design and use an input mask on the field. You should be able to specify whether you want an alpha or number for each character used.

 

[laurat]

I tried out the input mask but it did not work correctly. Can you please tell me what you had in mind for that or if you have any other suggestions.

Also, to everyone, I have another field that definately has to be converted to numeric, because users want calculations done. The only way for me to convert it to numeric is to go in and change all text data in that field to numeric???

 

[David R]

Input Mask: Check in help, but I believe you want the entries to be "9999999999999...(as long as the field is wide)999". 9 is input mask for an optional 0-9 character. 0 is a mandatory 0-9 character.

CInt() (there are other functions if the data isn't Integers; CLong, etc) can convert numeric "text" to real numbers usually. Give it a try in a regular query first. If it works, you can run an update query to replace the old values with new. Make a backup!!

It is possible that you can get all the data into number-like text strings and then just convert the field from Text to Long Integer (or whatever). But I haven't tried that recently so I don't remember how well it works.