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;
Member Avatar for LastMitch

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.