Query Expression using Integer and Lookup

[RichUE]

I have two fields in a table called "Change". "Num" is an integer field and
"Prefix" is a lookup. The lookup field is an integer that indexes text in
another table. I want to combine these fields in a query to produce the
typical result "AB123" but only when the integer field is not null. What
expression do I require?
My initial efforts either add or concatenate two integers, neither of which
is helpful.

I am using Access 97.

 

[Lord Kelvan]

select [prefix] & [num] from [change] where not isnull([num])

hope this helps

Regards
Kelvan

 

[RichUE]

Hi Kelvan

Sorry but this produces one of the results I was getting. It combines two
integers, whereas what I want is for the first integer to "look up" the text
result. This may not explain clearly so try this:
The Prefix field in table t_Change contains a single digit integer (e.g.
1,2,3,4) which is a lookup to a Letter field (e.g. AB,CD,EF,GH) in table
t_Prefix_type. The num field is an integer in table t_Change (e.g. 1234). The
result I require is "AB1234", being a combination of Letter and num fields.

 

[John Spencer]

Add the Prefix table to your query

SELECT PrefixTable.TextValue & Num as MyValue
FROM Change LEFT JOIN PrefixTable
ON Change.Prefix = PrefixTable.Prefix

If you don't want to add the table (best solution) then you can use the
DLookup function.

In a field "cell" in design view, enter an expression like the following using
your field and table names.

Field: MyValue: DLookup("TextValue","PrefixTable","Prefix=" & Prefix) & Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County