Access Report Question

[merlin]

I've got 2 tables and a report I could use a hand with. Table 1
contains a number and a name associated with that number. Talbe 2
contains the same number, but with different fields. What I'm trying
to do is simply display the fields in Table 2, but instead of the
number, I want it to display the name instead. I c++ it would be
sometime like:
switch (id)
{
case 1:
cout >> "Tom;
break;
case 2:
cout >> "Mike";
break;
case 3:
cout >> "Lee";
break;
}

Any suggestions for doing something similar in Access?

Thank you kindly,
David

 

[Allen Browne]

The VBA equivalent looks like this:

Function ShowTextForCode(id as variant) As variant
Select Case id
Case 1
ShowTextForCode = "Tom"
Case 2
ShowTextForCode = "Mike"
Case Else
ShowTextForCode = Null
End Select
End Function

However, it would be easier to create a query, and JOIN the 2 tables on the
number field. The query can then output the right name from Table1, without
the need to write code and (more importantly) without the need to maintain
the code whenever a new name is added.

If the number is optional in Table2, you need to use an outer join. If
that's a new concept, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[merlin]

Allen, that is exactly what I was looking for. Thank you so much!

~ David