Data Type Mismatch in criteria expression

[gaker10]

I've seen posts on this error before, but none of them helped me solve my problem. Here's my SQL query code:

SELECT BOMSearch.[BOM Qty], BOMSearch.[BOM UOM], MinPrices.PartNumber, MinPrices.[MinOfUnit Price], MinPrices.MinBuy, MinPrices.MinBuyUOM, MinPrices.[LeadTime(Weeks)], MinPrices.ExpirationDate
FROM MinPrices INNER JOIN BOMSearch ON (MinPrices.PartNumber = BOMSearch.[Part Number]) AND (MinPrices.UOM = BOMSearch.[BOM UOM])
WHERE (((BOMSearch.[BOM Qty])>Qtys.PriceBreakBegin And (BOMSearch.[BOM Qty])<Qtys.PriceBreakEnd) And ((BOMSearch.[BOM UOM])=MinPrices.UOM) And ((MinPrices.PartNumber)=BOMSearch.[Part Number]) And ((MinPrices.ExpirationDate)>=BOMSearch.[DUE DATE]));

The comparison is from a linked excel which has 5 fields which have matching formats to the formats in my table. I suspect it is my ExpirationDate field, which is a calculated field, but is formatted as a Date/Time in the Access table. I just don't know where the error is occurring.

I'd prefer to not delete any fields, as I need all of them.

Ideas?

[June7]

Formatted how? With Format function or with Format property? What is the expression that calcs the ExpirationDate?

Format function results in text value, not a true date. Try:

And ((CDate(MinPrices.ExpirationDate))>=BOMSearch.[DUE DATE]));

[gaker10]

Format Property. The expression is DateOfQuote + PriceValidation(Days) = ExpirationDate, where DateOfQuote is a Date, and PriceValidation is a number.

I tried your suggestion and it worked

Now, though, I'm not getting any results in my testing phase. I put a part number and quantity in my linked excel that I know is in my database and it is not finding any matches. My linked Excel looks like this:



I overwrote the PartNumber, but the actual part in that cell is in my database, with a pricebreakbegin of 1, and a pricebreakend of 10. I put in 2, so that should be fine. Both UOM are EA. The due date of the Q# in column A is 2/27/15, whereas my part in access expires in 12/31/15, so that field comparison is ok. Is my SQL correct for the comparisons I need to do?

[gaker10]

UPDATE via Post#3: My Due date vs Expiration Date should be: ((CDate(MinPrices.ExpirationDate))>=CDate(BOMSearch.[DUE DATE])));