I have a report that is an item listing with up to 4 ship dates. The dates are unique by vendor and vendor items may or may not be available for the 4 dates. Vendor may also have 1,2,3 or 4 dates. Maximum is 4.
I am showing the report by vendor with vendor # and Name as grouping. The detail is the item information. The headers for each grouping is
Item# Desc Pack Size Allowance Cost date1(mm/dd/yy) date2(optional) date3(opt) date4(opt)
For the detail I am showing either a blank space if available and an X if unavailable.
For Vendors that have less than 4 ship dates I am not display column header. if available the actual date is displayed.
My problem is not displaying the X in the detail section when the corresponding ship date doesn't exist
ex.
Item# Desc Pack Size Allowance Cost 6/3/2013 7/1/2012
123 red toy 12 2oz 1.15 8.19 X X X
My report record source is:
SELECT Vendors.Vendor_num,
Vendors.Name,
Item.item,
Item.Desc,
Item.Pack,
Item.item_size,
Item.allowance,
Item.points,
Item.Retailer_Price,
Item.Cust_Use,
IIf(IsNull([item.Del1_date]),"X","") AS del1st_date,
IIf(IsNull([item.Del2_date]),"X","") AS del2nd_date,
IIf(IsNull([item.Del3_date]),"X","") AS del3rd_date,
IIf(IsNull([item.Del4_date]),"X","") AS del4th_date,
Vendors.del1_date,
Vendors.del2_date,
Vendors.Del3_date,
Vendors.Del4_date
FROM Vendors
LEFT JOIN Item ON Vendors.[Vendor_num] = Item.[Vendor_num];
I think I need a if then elseif kind of fix here. I am not an access developer. I am a VB programmer but this is being asked for as an Access Report. Any help would be most appreciated.
Tom