We help IT Professionals succeed at work.

Get error Type MIsmatch in Criteria Expression when left join date

repco
repco asked
on
hello,
i have a table CustomerOrders in which it has Item, Due as fields.
I'm trying to left join a query (InvoiceItems) in which it has Item (text),OrderDue (date)
Orderdue though is being calculated as OrderDue: DateAdd("d",ORDERS!DUE,"28-Dec-1800") because the ORDERS!DUE is a number and converting to Date

i left join and it does find the results but on the null values, it gives me an #ERROR in the InvoiceItems.OrderDue field.
I'm trying to set up the criteria so that OrderDue is null but i get Type Mismatch in criteria. How cani fix?

SELECT CustomerOrders.Due, InvoiceItems.OrderDue
FROM CustomerOrders LEFT JOIN InvoiceItems ON CustomerOrders.Due = InvoiceItems.OrderDue
WHERE (((InvoiceItems.OrderDue) Is Null));
Comment
Watch Question

What's the data type for CustomerOrders.Due ?
Scott C

Author

Commented:
Date/Time
this part of your code -  DateAdd("d",ORDERS!DUE,"28-Dec-1800")   - is the Orders!Due ever a null?

Author

Commented:
In the invoiceitems query it has OrderDue: DateAdd("d",ORDERS!DUE,"28-Dec-1800") because the ORDERS!DUE is a number and converting to Date. I dont see any Null values in that query.

what i'm trying to search for is if CustomerOrders.Due contains any data that is not in InvoiceItems query. thus setting the InvoiceItems.Orderdue is null as critiera.
Well, I'm trying to figure out what the error is #error.  Your left join is causing the type mismatch.  I'm not sure why, but examine each of your query sections (joins) and determine exactly what your trying to relate.

Scott C

Author

Commented:
This is how my results look like if i take out the Is Null on the criteria for InvoiceItems.OrderDue

CustomerOrders.Due      InvoiceItems.OrderDue
                               #Error
                               #Error
6/23/2010                               6/23/2010
6/23/2010                               6/23/2010

Author

Commented:
the #error should move more to the right towards InvoiceItems.OrderDue....sorry
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try using:

OrderDue: DateAdd("d",Nz(ORDERS!DUE,0),"28-Dec-1800")

/gustav

Author

Commented:
thanks cactus data, seems though to change result from #error to 12/28/1800 so i really cant use the criteria a null value. is there any other solution?

Due      OrderDue
      12/28/1800
      12/28/1800
6/23/2010      6/23/2010
6/23/2010      6/23/2010

6/23/2010      6/23/2010
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It could be:

OrderDue: IIf(IsNull(ORDERS!DUE),Null,DateAdd("d",ORDERS!DUE,#12/28/1800#))

Note too, that date strings should be avoided.

/gustav

Author

Commented:
Perfect! Thanks!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav