Concatenation simple?

[hans37]

Concatenation simple?

For the sake of simplicity i have two tables: members and countries. The table countries consists of two fields: code, countryname. For each member in the table members there is a field countrycode. It contains the code (a number) but displays the countryname. This is aranged bij the lookup function. There are no forms.

Now I want to make a query on the table members that also has an expression field wich should give as result: lives in ... (where ... is the countryname)

When I make the expression (a simple concatenation): "lives in " & [members]![countrycode]

the result is (for example): lives in 1

so the expression gives the codevalue instead of the lookup value.

What should I do?

 

[tlbroadbent]

You'll need to JOIN the two tables and get the value from the countries table.

Select [members].[member] & " lives in " & [countries].[Countryname] As LivesInTxt
From members Inner Join countries
On [members].[countrycode]=[countries].

 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time. 
[COLOR=maroon]NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.[/color]

 

[hans37]

Thank you for your solution. It works. But it does not solve the whole problem (which is more complicated). See therefore an ohter question of me "how to specify a specific join in a query". But again, thanks.