Link to home
Start Free TrialLog in
Avatar of Bobby
BobbyFlag for United States of America

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.

Ship To Name: IIf(IsNull([Orders].[Company]),[Orders].[Name],[Orders].[Company])

Open in new window


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));

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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]

Open in new window

or simply:

Ship To Name: NZ([Orders].[Company],[Orders].[Name])
Avatar of Bobby

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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bobby

ASKER

That worked. Thanks very much.
You are welcome!