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;