By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,111 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,111 IT Pros & Developers. It's quick & easy.

Combining two lookup fields

P: n/a
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?

Jun 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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?

Jun 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.