Left joint query type mismatch

[samthomasny]

Working with two tables - tbl_A_OrdData & tbl_B_ShipData
tbl_A have all the orders placed, but tbl_B only have the orders that got shipped. I'm trying to create a query that will show me all orders that got placed for a particular criteria and if the order find a match in tbl_B to give me the ship date too. A left joint query get me the data I need. But my dates are in YYYYMMDD number format so in the query I am putting the DateValue function. And that error out as "data type mismatch" because it cannot find the shipdate for certain records. I tried this but didnt work. ShipDt: IIf (IsNull ([PSHPDT]), "", DateValue(Mid([PSHPDT],5,2) & "/" & Right([PSHPDT],2) & "/" & Left([PSHPDT],4)))

Can someone please give me some ideas?

 

[CJ_London]

the reason is that if PSHPDT is null you are returning a string value (""). which is not a date type.

Dates are numeric, so try changing the "" to 0

 

[namliam]

I just want to urge you to use Dateserial over DateValue, much more consistant and less prown to date issues.

Try 0 or even return NULL, should work for date fields as well.