Export of field gives me ID # rather than text

[LTillner]

I'm working with an old table design that was a single table (tblMain) and then "look up" tables for vendors, transportation carriers, users, etc, etc.

I'm being limited by the way these "look ups" were integrated.

Ex.:
tblMain includes prjmgr_id field (as TEXT) (field displays prjmgr name not id #)

tblprjmgr has fields
prjmgr_id (as autonumber)
prjmgr (as text)

Not a problem so far, but when I need to do quearies for exports (to Excel, etc), I get the prjmgr_id field as TEXT (the Project Manager name) on the query screen and the ID number in the export file.

When I try to link tblprjmgr into the query so I can get the prjmgr field (names) in the Export file, I get type Mismatch errors and can't link prjmgr_id (autonumber field) in tblprjmgr to prjmgr_id (text field) in tblmain

IS there any way around this error?

Thank you,

Lyne

 

[LTillner]

PS - I'm using Access 2003 but this is an Access 2000 mdb.

 

[Remou]

It seems that you have run into the anti-feature of look-up tables. Is it possible for you to change these fields to normal numeric fields?

 

[LTillner]

Not and keep the current form design/VBA code, etc.

I'm redesigning the whole thing, eventually. What I need is a short term work around. The best I found so far, was to create a copy of the "look up" table and modify it so that the autonumber field was a text field. Then I created a query with that table linked.

This work around creates extra work whenever records are added to the original tables!

 

[Remou]

Here is a possibility. Create a query based on the main table. For each of the look-up fields, right-click the query design grid and choose textbox on the look-up tab. This will allow you to join the real look-up tables in the usual way.