Combining two lookup fields

[John Smith]

I am trying to combine two lookup fields into one field for display on a
form and a report. I understand the basics behind how to do this:

I set the control source for a new text box control to
=[HabitID] & [ClassID]

The problem is, this gets me a number. You see, HabitID and ClassID are both
lookup fields on the Control Source table to the form. So, if I have a field
on the form named HabitID, it would be a combobox that looks up values from
tblHabit. Thus, the user sees a list of habits but a number is actually
stored in the table. How can I get this new field set up to combine the two
fields HabitID and ClassID and convert it to its text equivalents?

[Allen Browne]

If you also have combos on this form that have the text values in their 2nd
column, you could read the values from the Column() of the combo:
=[cboHabitID].Column(1) & " " & [cboClassID].Column(1)

If you don't have those, you could use DLookup() to read the value from the
respective tables:
=DLookup("HabitName", "tblHabit", "HabitID = " & Nz([HabitID],0) & " " &
DLookup("ClassName", "tblClass", "ClassID = " & Nz([ClassID],0)

For more info on how to use DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Smith" <so*****@microsoft.com> wrote in message
news:2h*********************@fe07.news.easynews.co m...

I am trying to combine two lookup fields into one field for display on a
form and a report. I understand the basics behind how to do this:

I set the control source for a new text box control to
=[HabitID] & [ClassID]

The problem is, this gets me a number. You see, HabitID and ClassID are
both lookup fields on the Control Source table to the form. So, if I have
a field on the form named HabitID, it would be a combobox that looks up
values from tblHabit. Thus, the user sees a list of habits but a number is
actually stored in the table. How can I get this new field set up to
combine the two fields HabitID and ClassID and convert it to its text
equivalents?