Sql iif statement, column?

[compooper]

Hi folks,

I'm trying to write a query with an IIF statement that displays service names.

SELECT IIf([TestTable].[Svc_SubAcct]=9,[Testtable].[Svc_ComName],[Testtable].[Svc_SubAcct]) AS Service, Count(IIf([TestTable].[Svc_SubAcct]=9,[Testtable].[Svc_ComName],[Testtable].[Svc_SubAcct])) AS [Count], TestTable.CompanyID, TestTable.InvTypeID, TestTable.InvPeriod
FROM TestTable
GROUP BY IIf([TestTable].[Svc_SubAcct]=9,[Testtable].[Svc_ComName],[Testtable].[Svc_SubAcct]), TestTable.CompanyID, TestTable.InvTypeID, TestTable.InvPeriod;

Problem:
Testtable.Svc_SubAcct is a combobox/lookup field
[Testtable].[Svc_ComName] is a combobox/lookup field
The query is displaying the number column vs. the text column that I want it to display
I know in vba I can refer to the column using .column(#)
But that doesn't seem to work in SQL.


ANy ideas?

[orange]

Please explain the combobox/lookup field.

Lookups (combo and listboxes) on forms are not an issue and are very common. However, lookup fields at the table level are the problem; this site
http://access.mvps.org/access/lookupfields.htm
explains why.

[compooper]

Testtable.Svc_SubAcct is a Number field with a lookup field:
SELECT [GLSUBID], [GLsubheading] FROM [GLaccounts-sub] ORDER BY [GLsubheading];

[Testtable].[Svc_ComName] is similiar

Is there anyway to select a column in VBA when creating a pivottable?

[orange]

I don't understand this statement

Testtable.Svc_SubAcct is a Number field with a lookup field: Tell me about the lookup field.

[compooper]

Okay let me clarify:

Testtable.Svc_SubAcct
Data Type: Number
Row Source: SELECT [GLSUBID], [GLsubheading] FROM [GLaccounts-sub] ORDER BY [GLsubheading];