how to convert lookup values to the "display text"

[ccorley]

I'm using an sql code (below) which uses a few lookup fields. Unfortunately
in the datasheet view, I get the "bound values" instead of the "display
values". How can I change the properties for the these lookup fields so I
can see the "display values" from the datasheet view?



SELECT
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [Form_9_Status]

UNION ALL SELECT
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [TDY_Status]

UNION ALL SELECT
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [Order_Status] INNER JOIN [Funding] ON Order_Status.Funding = Funding.ID;

 

[Daryl S]

Ccorley -

You do this by joining the lookup table in each SELECT. Assuming you have a
lookup table called [ExpenseTypes] and you want the field
[Expense_Description] to show instead of [Expense_Type], and all tables have
[Expense_Type] populated, you change the query as follows:


SELECT
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM [Form_9_Status] INNER JOIN [ExpenseTypes] ON
[Form_9_Status].[Expense_Type] = [ExpenseTypes].[Expense_Type]

UNION ALL SELECT
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM [TDY_Status] INNER JOIN [ExpenseTypes] ON [TDY_Status].[Expense_Type] =
[ExpenseTypes].[Expense_Type]

UNION ALL SELECT
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM ([Order_Status] INNER JOIN [Funding] ON Order_Status.Funding =
Funding.ID) INNER JOIN [ExpenseTypes] ON [Order_Status].[Expense_Type] =
[ExpenseTypes].[Expense_Type];

If not all tables have [Expense_Type] populated, then you need an inner join
instead. Here is a sample:
SELECT
[Funding],[Date],[Description],[Company],nz([Expense_Description],""),[Amount],[Status]
FROM [Form_9_Status] LEFT JOIN [ExpenseTypes] ON
[Form_9_Status].[Expense_Type] = [ExpenseTypes].[Expense_Type]

You will need to make these changes for each lookup table you have.