Exclude Column in the combobox (1 Viewer)

[Ksabai]

I have this following Access Query


SELECT tblcontract.DtShip01 AS ShipDate, tblcontract.ContNo, tblpayments.SMonth
FROM tblcontract LEFT JOIN tblpayments ON tblcontract.ContNo = tblpayments.ContNo
WHERE (((tblcontract.DtShip01) Is Not Null) AND ((tblcontract.ContNo)=[Forms]![Payments]![ContNo]));

UNION SELECT tblcontract.DtShip02 AS ShipDate, tblcontract.ContNo, tblpayments.SMonth
FROM tblcontract LEFT JOIN tblpayments ON tblcontract.ContNo = tblpayments.ContNo
WHERE (((tblcontract.DtShip02) Is Not Null) AND ((tblcontract.ContNo)=[Forms]![Payments]![ContNo]));

UNION SELECT tblcontract.DtShip03 AS ShipDate, tblcontract.ContNo, tblpayments.SMonth
FROM tblcontract LEFT JOIN tblpayments ON tblcontract.ContNo = tblpayments.ContNo
WHERE (((tblcontract.DtShip03) Is Not Null) AND ((tblcontract.ContNo)=[Forms]![Payments]![ContNo]));

UNION SELECT tblcontract.DtShip04 AS ShipDate, tblcontract.ContNo, tblpayments.SMonth
FROM tblcontract LEFT JOIN tblpayments ON tblcontract.ContNo = tblpayments.ContNo
WHERE (((tblcontract.DtShip04) Is Not Null) AND ((tblcontract.ContNo)=[Forms]![Payments]![ContNo]));


It Shows me all the Ship Dates from tbl Contract, i want the Ship Dates in the tblPayments to be excluded and the rest to be shown in the combo box.



Request someones help.

 

[June7]

So if a ShipDate is also a date value in tblPayments, don't include that date in combobox list? What is name of date field in tblPayments? What is name of that UNION query?

 

[Ksabai]

Yes SMonth in tblPayments is also a Date Field. I want only ShipDate04, ShipDate03 and ShipDate02 to display in the combo box if Shipdate01 is in SMonth for that particular ContNo


Hope iam clear now

 

[Ksabai]

Tried



Not In (Select Nz(SMonth,'') from [tblpayments] Where ContNo = [Forms]![Payments]![ContNo])



But Not Working

 

[missinglinq]

Why not simply set the Column Width of the Columns showing the Ship Dates from tblPaymentsto to 0?

Linq ;0)>

 

[June7]

You don't want DtShip01 record if DtShip01 = sMonth? So include that criteria in the first SELECT.