I know this is resolved but I just want to share this workaround which I had suggested to my I.T. Manager many years ago when he was asking me why his "between" is not working, I had used DateValue for this, something like

Code:
WHERE DateValue(Shipments.ShipmentDate)=#" & DateValue("07/16/2009") & "#"
Code:
WHERE DateValue(Shipments.ShipmentDate BETWEEN #"  & DateValue("07/10/2009") & "# AND #"  & DateValue("07/16/2009") & "#"