IIF statement to select proper column data
Windows XP 
Access 2007
I have a simple DB that I am working on that contains 3 date fields. Using these fields I want to be able to select the correct column based on a set criteria. I thought I had it figured out, however, I am not getting the right info to populate my new column.
Here is a brief description of what I am looking for, followed by the code I used.
The three date columns are "Estimated Ship Date", "Ready to Ship Date" and "Actual Ship Date" and I am basing the decision criteria on a column named "Product Type". I am looking to first determine whether or not the "product Type" column is not equal to "Small Crawler" or "Large Crawler", if it is not then I want to use the "Actual Ship Date" if it's populated and the "Estimated Ship Date" if not. If it is equal to "Small Crawler" or "Large Crawler", then I want to pull in the "Ready to Ship Date" if it's populated and the "Actual Ship Date" if not.
Inv Date: IIf([Product Type]<>"Small Lattice" Or "Large Lattice",(IIf(IsNull([Actual Ship Date]),[Estimated Ship Date],[Actual Ship Date])),(IIf(IsNull([Ready to ship date]),[Estimated Ship Date],[Ready to ship date])))
Thanks for the help!
Lance
Reply With Quote
