"Data type mismatch in criteria expression" Error

[jherbert]

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;

 

[billmeye]

Have you reduced your query down to the point where you don't get the error? That should lead you to knowing what syntax is off. Also, for performing date math try using the DateDiff function.

[ETD_Date]-[Date Stamp] AS Day_1

DateDiff("d",[Date Stamp],[ETD_Date]) AS Day_1

 

[jherbert]

Thanks for the response billmeye,

If I take the "Day_1" column out of the query it works. I also tried the "datediff" function and I still get the same error.

 

[billmeye]

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

Maybe it just doesn't see the Alias ETD_Date as a date data type. Have you tried surrounding it with CDate()?

 

[SOS]

I usually find that error means that the field in question has a null value somewhere.