Data Type changes from text to number automatically

[Udaman]

I tried searching the forums for the above title, and all I get is a bunch of excel posts that have nothing to do with what I am searching for. So I apologize if this question was posted before, but even a Google search turns up no results...I blame the Yahoo hack!

Anyways, I have two tables called Mechanics and EmployeeTimeSheet.

In the Mechanics table I have several fields, but the ones in particular are MechID that has an autonumber data type, and Initials which has a short text data type. The data in the initials column are all text.

In the EmployeeTimeSheet table, I have a field called MechName, and I initially set the data type to short text. I then use the Lookup Wizard to search for the initials from the Mechanics table, and I sort by Initials. As soon as I press finish though, the data type changes to a number. Why is this? All the data is text, there isn't a number anywhere. I started looking deeper into my tables and found this phenomenon in several fields where all the data is text, but it's being changed to a number, and it's really messing me up on my forms.

Can anyone explain why this is happening? I have deleted relationships and changed the data types back to short text, but as soon as I use the Lookup Wizard to create a list, it changes back to a number again. I have also noticed that the column count jumps from 1 to 2 when it changes to a number.

 

[xenou]

It sounds like you are using a LOOKUP in a TABLE. I'd personally advise you never to do this. Lookups belong in forms, not tables.

To explain what is happening, a lookup works like this:

1 Apple
2 Banana
3 Peach
4 Cherry

When you "lookup" Banana, you get the result of it's numeric value (the key column) which is 2. That is the "real value" that will be stored as a result of this lookup. I don't know precisely how lookups work in tables but it makes sense that "under the hood" there is somewhere two columns - one for the lookup value and one for the key value.

 

[Udaman]

So if I understand correctly, you only want to use lookups in your tables if you plan to fill out the data without using a form, so you can utilize drop down lists instead of typing all of the data, correct?

So what I did was I opened my table, deleted any relationships I had with the field, and changed the data type back to short text (I actually had to change it to long text in order to wipe out the lookup tab, then change it back to short text). When I opened my form, it still remembered that I was pulling from another table, so I didn't have to make any modifications to my form, and it worked, the data was there in text format. The only drawback is I now have to go back through all my tables with those fields, and type in the text value to replace the numerical value that was there instead.

Of all the tutorials I watched and forums I have read, this is the first time anyone advised me not to use lookups in my table...wish I had known this sooner!

Thanks so much for the explanation and advice!

 

[Udaman]

Upon further delving, it appears that to do what you say, I have to swap out all of my bound controls for Unbound combo boxes, which broke everything. I need those combo boxes to be bound to my table. So I guess my question is, why do you recommend never using lookups in a table, when that is the only way to have it bound (that my limited knowledge is aware of)? It seems I need lots of VBA to emulate what is already built into the program, specifically to have the combo box retain it's value when you close and reopen the form. If it's bound, it remembers the value with no VBA necessary. I have hundreds of bound lookups in my forms, there is no way I am going to swap them all out and then code them to act like it. I think I need a better understanding of how to use lookups in my table that will retain the short text data type instead, unless I am totally confused as to what is happening. Is there a more in-depth explanation that can hopefully help me to understand my dilemma? I have many lookups that have the short text data type, and they work great. It's only a few that are being converted over to a number data type that are giving me a hard time.

 

[xenou]

Hi, I said I don't recommend to use Lookups in TABLES. I said nothing about forms. Or rather, In fact, I said lookups belong in forms. You can (and often) should use bound controls with lookups in your forms.

I don't know what you have actually done here - it sounds like you are using Table lookups to get yourself some kind of desired behavior in your forms. Whatever, that's why you have numbers. The number is the value that comes from the relationship established by the lookup.

Think about what a lookup is ... you are looking up one value (such as a person's name) to get another (such as their telephone number). If you just want the person's name in the first place, you don't need to use a lookup.

 

[Udaman]

What is odd is that for every lookup I have created, I have used the exact same procedure, yet I am getting different results (which to me is the definition of insanity). Sometimes it keeps the short text data type, and other times it converts it to a number data type. Every table has an ID that is keyed, and the fields in the tables that the lookups are pulling from are formatted as short text. So why it's not consistent is what is boggling my mind. There must be some small item I am not seeing that is making the difference, I wish not to believe the program is just bugged, and I am pretty sure the bug is in my brain.

So maybe help me to understand this... After I created the lookup for the field in my table (using the wizard), in the Lookup tab at the bottom is an option for Bound Column. By default after I create the lookup, the value is set to 1 (column count is 2). What happens when I set that value to 2? I can't see any visible difference when I look at my table in datasheet view, and my query gives me the same results, so maybe if I can better understand what changing that value does, might help shed some light on things.

 

[xenou]

The bound column refers to what the real value is that is stored.
If I use the phone book example (let's say its a simple phone book with only last name and phone number), then do you want the bound column to be the Name or the Phone Number? If bound column is one, then its the first, if bound column is 2 its the second.

 

[Udaman]

I finally believe I was able to figure out what is going on for any who might have this issue in the future.

When I created most of my tables, the very first thing was to name my ID with auto-number data type, and made it my key. I then created my other fields.

For some reason I must have created some tables without an ID key. So in Table A that was keyed I would use a lookup for a field that pulled from another table. Well, I did the same thing in table B that was not keyed, and the result is that the data type in Table A was converted to a number, but the data type in table B stayed as Short Text. Even though both forms and tables were showing the same information, when I would try to pre-populate a field from one form to another, I was getting a data mismatch errors, and for the life could not figure out why....well, now I know why.

So as a result I just had to bite the bullet and make sure all my tables were set with keyed ID's, and reset the lookup's for this one particular field for all tables that had it. The result in my case was a deletion of over 500 records. Before I made my changes though I made sure to first backup my database, then export all my tables to Excel so I can more quickly repopulate the data that was deleted.

Thanks so much for the help, and I hope this might prevent others from making the same mistake.