Bobby
asked on
Access 2003 query, iif not functioning upon export
I have the following in a query design view, which if I just double click the query to see the results, it works fine (I see correct data in all records). But, if I export the query to Excel or a txt file, the iif isn't working... there are 4 records that do not have Company, so they should return Name because Company is null. Again, this works fine when just executing the query, but does nothing (just shows Company, null or not) if I export it.
Here is the SQL view:
Ship To Name: IIf(IsNull([Orders].[Company]),[Orders].[Name],[Orders].[Company])
Here is the SQL view:
SELECT Customers.CustomerID AS [Ship To ID], IIf(IsNull([Orders].[Company]),[Orders].[Name],[Orders].[Company]) AS [Ship To Name], Orders.Address AS [Ship To Address 1], Orders.Address2 AS [Ship To Address2], Orders.City AS [Ship To City], Orders.State AS [Ship To State], Orders.Zip AS [Ship To Zip Code], Orders.Phone AS [Ship To Phone Number], Orders.OrderNumber AS [Invoice Number/Credit Number], [Order Details].ItemNumber AS [Invoice Line Number], Orders.LocalSortDate3 AS [Invoice Date], [Order Details].SKU AS [Your Item Number], InventorySuppliers.SupplierSKU AS [MA Item Number], [Order Details].Product AS [Item Description], [Order Details].QuantityShipped AS Qty, InventoryProfile.SoldAs AS UOM, InventorySuppliers.Cost AS [Your Unit Purchase Price]
FROM ((([Order Details] INNER JOIN Orders ON [Order Details].OrderNumber = Orders.OrderNumber) INNER JOIN InventorySuppliers ON [Order Details].SKU = InventorySuppliers.LocalSKU) INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN InventoryProfile ON InventorySuppliers.LocalSKU = InventoryProfile.LocalSKU
WHERE (((Orders.LocalSortDate3) Between DateSerial(Year(DateAdd("m",-1,Now())),Month(DateAdd("m",-1,Now())),1) And DateSerial(Year(DateAdd("m",-1,Now())),Month(DateAdd("m",-1,Now()))+1,0)) AND ((InventorySuppliers.SupplierID)=279) AND ((Orders.Approved)=True));
I am thinking because Iif is a dynamic operation ...it doesn't evaluate when you are exporting...probably if you make a temp table out of the query and export it it should work
Try with Nz and compare with an empty string:
IIf(Nz([Orders].[Company])="",[Orders].[Name],[Orders].[Company]) AS [Ship To Name]
or simply:
Ship To Name: NZ([Orders].[Company],[Ord ers].[Name ])
Ship To Name: NZ([Orders].[Company],[Ord
ASKER
Gustav,
I got a syntax error when trying that.
Dale,
No errors but no difference either, still empty records when i export it to Excel.
I got a syntax error when trying that.
Dale,
No errors but no difference either, still empty records when i export it to Excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked. Thanks very much.
You are welcome!