Criteria Date > #MM/DD/YYYY# Giving Me Data Type Mismatch

[xljunkie]

TableEQP has a field called SoldDate as Double and comes with numbers like 0, 20141215, 20150403, etc.

In a query, I am doing this:

SoldDate2: IIf([SoldDate]=0,"",DateSerial(Left([SoldDate],4),Left(Right([SoldDate],4),2),Right([SoldDate],2)))

When I look at the results, it looks fine. Dates look like dates, and if there was a 0 populated in TableEQP, it is returning a blank record. Now, when I attempt to add in a criteria >#8/31/2014# the query doesn't run and gives me Data Type Mismatch. What can I do? I know this error happens with formatting.

 

[Joe4]

I don't think it likes the fact you are returning a non-date value for some records (the "" returns a non-date value).
Try returning a date that will never be returned by your criteria, i.e. so artificially small date, like:

[COLOR=#333333]SoldDate2: IIf([SoldDate]=0,DateSerial(1900,1,1),DateSerial(Left([SoldDate],4),Mid([SoldDate],4,2),Right([SoldDate],2)))[/COLOR]

BTW, Access has a MID function. So this:
Left(Right([SoldDate],4),2)
can be replaced by:
Mid([SoldDate],4,2)

 

[xljunkie]

Yeah I tried that earlier and many other variations. None of them worked. But what ended up working for me was changing my query to a make table query and using the resulting table. Then I could filter just fine.

 

[Joe4]

My apologies, I had a typo in my formula. Using the MID function, we would be starting at the 5th position, not the 4th.
So just use:

[COLOR=#333333]SoldDate2: IIf([SoldDate]=0,DateSerial(1900,1,1),DateSerial(Left([SoldDate],4),Mid([SoldDate],[/COLOR][COLOR=#ff0000]5[/COLOR][COLOR=#333333],2),Right([SoldDate],2)))[/COLOR]

I tested it out on the examples that you gave, and it works.

If it still does not work, that is probably indicative of a data show, i.e. a blank entry or other entry that cannot be converted to a date.