Lookup Wizard changes field from text to numeric

[Scott D Harding via AccessMonster.com]

When I use the LUw to create a lup field from an existing table, it changes
the field from text to numeric, can't figure out how to fix.

 

[Jeff Boyce]

Douglas is correct -- many of us do hold the use of lookup data type in
tables in low regard.

I do believe you can do something about it, though...

If you change the data type of that field to one compatible with the
underlying (looked up) record's primary key, you will see, in the table, the
looked up record's primary key (this is what is actually stored there
anyway).

Then, instead of trying to do data entry/edit directly in the table (this is
NOT a good idea - Access tables store data, Access forms & reports display
it), use a form and add a combobox control to that form to display the
looked up value (on the form), while still saving the looked up value's
record's primary key value in your table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Scott D Harding via AccessMonster.com]

Thanks Doug for your input, yes, and it changes the fields property to all
numeric properties as well. When I would try to correct, it, of course says
that you must delete the link that it created. Would there be a work-around
that I could use?

 

[Douglas J. Steele]

Well, my first piece of advice would be not to use lookup fields.

Assuming you go along with that, realistically you do want the foreign key
to be numeric, as it'll be smaller.

If you use a form to display your data, you can always bind the numeric
field to a combo box that displays the text.

For other uses (such as in reports), create a query that joins your two
tables in order to get the description out of the lookup table.

 

[Scott D Harding via AccessMonster.com]

Doug, I think I just found a solution. Use the wizard, pick you field that
you want to look up. Then when you exit, it gives you the save box. You
indicate "No" change the field box back to text, then save. It just worked on
a tezt DB I was working with.

Thanks Doug for your input, yes, and it changes the fields property to all
numeric properties as well. When I would try to correct, it, of course says
that you must delete the link that it created. Would there be a work-around
that I could use?

Are you talking about the fact that while it may look as though you've got
Scott in the field, what's actually stored is the ID of Scott from the

[quoted text clipped - 7 lines]