0

I am currently working on a report and I am trying to build a query that keeps giving me this error: "Data type mismatch in criteria expression" In my first query, I am calculating the difference between two dates to get a day until our ship leaves port (ex. 6/12/13 minus 6/10/13 = 2 Days) with each revenue booking. I then created another query to sum all the revenue by the day. However, when I try to run the query, I get this error. For some reason it doesn't like me grouping by this calculated field. Does anyone know how I can get around this? Any help would be greatly appreciated!

Here is the code for the initial query(Query_Capacity_Calc_Voy_1):

SELECT 
    Table_Capacity_Original.VOYAGE_REFERENCE, 
    Query_Capacity_Total_Voy.Service_No, 
    Query_Capacity_Total_Voy.Direction, 
    Query_Capacity_Total_Voy.Sub_Service_No, 
    Table_Capacity_Original.[Date Stamp], 
    DateSerial(Nz(Left([Voyage_ETD_Date],4),"19000101"), nz(Mid([Voyage_ETD_Date],5,2),"19000101"), nz(Right([Voyage_ETD_Date],2),"19000101")) AS ETD_Date, 
    [ETD_Date]-[Date Stamp] AS Day_1, 
    Table_Capacity_Original.Revenue
FROM Query_Capacity_Total_Voy 
INNER JOIN (Table_Capacity_Original 
    INNER JOIN [Voyage-Results] 
    ON (Table_Capacity_Original.VOYAGE_REFERENCE = [Voyage-Results].VOYAGE_REFERENCE) 
    AND (Table_Capacity_Original.DIRECTION = [Voyage-Results].DIRECTION) 
    AND (Table_Capacity_Original.SERVICE_NO = [Voyage-Results].SERVICE_NO)) 
ON (Query_Capacity_Total_Voy.Service_No = Table_Capacity_Original.SERVICE_NO) 
AND (Query_Capacity_Total_Voy.Direction = Table_Capacity_Original.DIRECTION) 
AND (Query_Capacity_Total_Voy.Sub_Service_No = Table_Capacity_Original.Sub_Service_No);

Here is the code for the summing query (Query_Capacity_Calc_Voy_2):

SELECT 
    Query_Capacity_Calc_Voy_1.Service_No, 
    Query_Capacity_Calc_Voy_1.Direction, 
    Query_Capacity_Calc_Voy_1.Sub_Service_No, 
    Query_Capacity_Calc_Voy_1.Day_1, 
    Sum(Query_Capacity_Calc_Voy_1.Revenue) AS SumOfRevenue
FROM Query_Capacity_Calc_Voy_1 
INNER JOIN Query_Capacity_Total_Voy 
ON (Query_Capacity_Calc_Voy_1.Service_No = Query_Capacity_Total_Voy.Service_No) 
AND (Query_Capacity_Calc_Voy_1.Direction = Query_Capacity_Total_Voy.Direction) 
AND (Query_Capacity_Calc_Voy_1.Sub_Service_No = Query_Capacity_Total_Voy.Sub_Service_No)
GROUP BY 
    Query_Capacity_Calc_Voy_1.Service_No, 
    Query_Capacity_Calc_Voy_1.Direction, 
    Query_Capacity_Calc_Voy_1.Sub_Service_No, 
    Query_Capacity_Calc_Voy_1.Day_1;

Edited by pritaeas: Added markdown.

2
Contributors
1
Reply
15
Views
4 Years
Discussion Span
Last Post by LastMitch
0

Access: "Data type mismatch in criteria expression" Issue

The error means that your data is not going to the correct column. Anothers words double check your table see if the data goes in the correct column. Fix your query and make sure you are actually fetching that data. I don't have a DB to test it.

Edited by LastMitch

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.