Use the CASE statement as suggested. FYI if you need to know how Access 
communicates with SQL server, go into the ODBC configuration and turn 
TRACE on. Run your query and then turn TRACE off again. Analyze the log 
file. you will see how Access constructs its sql statements to talk to SQL 
server.
Nico


> I utilize the IIF extensively in MS Access.  I am currently converting 
l> ots of Access queries to SQL stored procedures and am having some 
p> roblems with utilizing the IIF in TSQL.  If anyone has any ideas after 
l> ooking at this please let me know what I am doing wrong.

> When I syntax check this sql statement:
I> NSERT INTO Xtable ( 
 >    company
 >   ,manifest
 >   ,manifest_date
 >   ,billed
 >   ,buyer_fein
 >   ,buyer_name
 >   ,buyer_st_id
 >   ,destination_code
 >   ,destination_city
 >   ,destination_state
 >   ,carrier_fein
 >   ,carrier_name
 >   ,destination_name
 >   ,gross
 >   ,invoice_number
 >   ,invoice_date
 >   ,mode_code
 >   ,net
 >   ,origin_code
 >   ,origin_city
 >   ,origin_state
 >   ,seller_fein
 >   ,seller_name
 >   ,seller_st_id
 >   ,schedule_code
 >   ,report_state
 >   ,report_year
 >   ,report_month
 >   ,product
 >   ,origin_name )
S> ELECT 
 >    'EML' AS company
 >   ,tblSuppliersSchedule.DocumentNumber AS manifest
 >   ,tblSuppliersSchedule.DateReceived_Shipped AS manifest_date
 >   ,tblSuppliersSchedule.BilledGallons AS billed
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable',
[> Purchaser_SellerFEIN],'522101198') AS buyer_fein
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable',
[> Purchaser_SellerName],'Energy Merchant LLC') AS buyer_name
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable',
[> Purchaser_SellerFEIN],'EML') AS buyer_st_id
 >   ,tblSuppliersSchedule.PtDestinationTerminalCode AS destination_code
 >   ,tblSuppliersSchedule.PtDestinationCity AS destination_city
 >   ,tblSuppliersSchedule.PtDestinationState AS destination_state
 >   ,tblSuppliersSchedule.CarrierFEIN AS carrier_fein
 >   ,tblSuppliersSchedule.CarrierName AS carrier_name
 >   ,IIF(ISNULL([PtDestinationName]),RTRIM([PtDestinationCity]) & ', ' & 
R> TRIM([PtDestinationState]),[PtDestinationName]) AS destination_name
 >   ,tblSuppliersSchedule.GrossGallons AS gross
 >   ,tblSuppliersSchedule.AccountsID AS invoice_number
 >   ,Accounts.TransDate AS invoice_date
 >   ,tblSuppliersSchedule.TransportationMode AS mode_code
 >   ,tblSuppliersSchedule.NetGallons AS net
 >   ,tblSuppliersSchedule.PtOriginTerminalCode AS origin_code
 >   ,tblSuppliersSchedule.PtOriginCity AS origin_city
 >   ,tblSuppliersSchedule.PtOriginState AS origin_state
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable','522101198',
[> tblSuppliersSchedule].[Purchaser_SellerFEIN]) AS seller_fein
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable','Energy Merchant 
L> LC',[tblSuppliersSchedule].[Purchaser_SellerName]) AS seller_name
 >   ,IIF([Accounts].[AccountsType]='Invoice/Receivable','EML',' ') AS 
s> eller_st_id
 >   ,tblSuppliersSchedule.ScheduleTypeCode
 >   ,tblSuppliersSchedule.JurisdictionCode
 >   ,YEAR([StartDate]) AS report_year
 >   ,MONTH([StartDate]) AS report_month
 >   ,tblSuppliersSchedule.ProductType AS product
 >   ,tblSuppliersSchedule.PtOriginName AS origin_name
 > FROM tblSuppliersSchedule 
I> NNER JOIN Accounts ON tblSuppliersSchedule.AccountsID = 
A> ccounts.AccountsID
W> HERE (tblSuppliersSchedule.JurisdictionCode='WV')

> 
I>  get this error message.

> Server: Msg 170, Level 15, State 1, Line 37
L> ine 37: Incorrect syntax near '='.

> Any help would be appreciated.  I have been battling with IIF for a 
while 
a> nd ususally found a way around it, however, there are a number of IIF's 
i> n this statement.

> Keith