Date criteria in a query (1 Viewer)

[Humpty]

Wonder if anyone can help with the syntax in this query? It always returns no results and I can't for the life of me understand why.

SELECT tblExpectedShipments.ShipmentDate
FROM tblExpectedShipments
WHERE (((tblExpectedShipments.ShipmentDate)=IIf([Forms]![frmSelectShipperReport]![chkOverdue]=0,(tblExpectedShipments.ShipmentDate)>Date(),(tblExpectedShipments.ShipmentDate)<Date())));

Any pointers would be greatly appreciated. Thanks.

 

[workmad3]

not entirely sure, but perhaps

SELECT tblExpectedShipments.ShipmentDate
FROM tblExpectedShipments
WHERE (((tblExpectedShipments.ShipmentDate)=#IIf([Forms]![frmSelectShipperReport]![chkOverdue]=0,(tblExpectedShipments.ShipmentDate)>Date(),(tblExpectedShipments.ShipmentDate)<Date())#));

Basically wrapping the result of the Iif with ## so that it is forced to be seen as a date value.

 

[Humpty]

Thanks workmad. It looked like it could be a possible solution but afraid it just returns the error "The expression you entered has and invalid date value". Any other thoughts?

 

[workmad3]

Hmm... of course it wouldn't. Should really check queries before changing them

tblExpectedShipments.ShipmentDate>Date() is a true/false boolean expression.

if you are comparing this to a date, then no you wouldn't ever get any results