Scott:
If you merely want to show the position in an unbound text box in a form or
report then you should look up the value from the (text) position column in a
query which joins the tEmployees table to the positions table. Your
expression will be the same, but with the query name in place of the
tEmployees table name. Another way would be to use nested DLookup function
calls, but that's very inefficient.
If, on the other hand, you are getting the position as a default and
assigning it to a column in another table (which, if I recall rightly was
what you were doing in an earlier thread) then you should be storing the
numeric value as a foreign key in that table in the same way as you are in
the tEmployees table, not the text value. If you use a surrogate numeric
primary key, as you have here, non-key columns should not be referenced by a
foreign key. The alternative would be to dispense with the autonumber column
and use the text column as the positions table's 'natural' primary key. That
way you don't need to bring the positions table in at all when looking up the
value as the value will of the foreign key in tEmployees will be the text
value. The positions table is then there to enforce referential integrity by
allowing only valid position values to be entered into foreign key columns in
tables which reference it.
Ken Sheridan
Stafford, England
channell said:
I have the following DLookup Function:
=DLookUp("[PRELOAD POSITION]","[tEMPLOYEES]","[Employee ID] = " & [Employee
ID])
[PRELOAD POSITION] is a FK in my tEMPLOYEES. This function is returning the
Number (AutoNumber) for Position ID, and not the actual text (Which I need).
If you could help, I greatly appreciate it very much!!
-Scott Channell