repco
asked on
Get error Type MIsmatch in Criteria Expression when left join date
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));
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
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));
ASKER
Date/Time
this part of your code - DateAdd("d",ORDERS!DUE,"28 -Dec-1800" ) - is the Orders!Due ever a null?
ASKER
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.
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
Scott C
ASKER
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
CustomerOrders.Due InvoiceItems.OrderDue
#Error
#Error
6/23/2010 6/23/2010
6/23/2010 6/23/2010
ASKER
the #error should move more to the right towards InvoiceItems.OrderDue....s orry
Try using:
OrderDue: DateAdd("d",Nz(ORDERS!DUE, 0),"28-Dec -1800")
/gustav
OrderDue: DateAdd("d",Nz(ORDERS!DUE,
/gustav
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thanks!
You are welcome!
/gustav
/gustav
Scott C