Joining date fields

[jcfraun]

I have a query with the following sql:

SELECT q_ReportMonths_RosettaKeywords.Page, q_ReportMonths_RosettaKeywords.Keyword, q_ReportMonths_RosettaKeywords.AssignedMonth, q_ExternalSummarybyMonth_Rosetta.SumOfSalesSameSession AS OrganicSales, q_InternalSummarybyDate_Rosetta.SumOfSales AS InternalSales, q_SummaryofPaidSales_Rosetta.SumOfSales AS PaidSales, q_ExternalSummarybyMonth_Rosetta.SumOfOrdersSameSession AS OrganicOrders, q_InternalSummarybyDate_Rosetta.SumOfOrders AS InternalOrders, q_SummaryofPaidSales_Rosetta.[SumOfNo Orders] AS PaidOrders, q_ExternalSummarybyMonth_Rosetta.SumOfVisitorsSameSession AS OrganicVisitors, q_InternalSummarybyDate_Rosetta.SumOfSearchingSessions AS InternalSearchingSessions, q_ExternalSummarybyMonth_Rosetta.AvgOfConversionRateSameSession AS OrganicConv, q_SummaryofPaidSales_Rosetta.SumOfImps AS PaidImps, q_ExternalSummarybyMonth_Rosetta.SumOfVisitorsSameSession, q_ExternalSummarybyMonth_Rosetta.AvgOfConversionRateSameSession, q_Rosetta_Rankings_Monthly.[Googlecom position], q_Rosetta_Rankings_Monthly.[Yahoo position], q_Rosetta_Rankings_Monthly.[Bing position]
FROM (((q_ReportMonths_RosettaKeywords LEFT JOIN q_SummaryofPaidSales_Rosetta ON (q_ReportMonths_RosettaKeywords.AssignedMonth = q_SummaryofPaidSales_Rosetta.AssignedMonth) AND (q_ReportMonths_RosettaKeywords.Keyword = q_SummaryofPaidSales_Rosetta.Keyword) AND (q_ReportMonths_RosettaKeywords.Page = q_SummaryofPaidSales_Rosetta.Page)) LEFT JOIN q_InternalSummarybyDate_Rosetta ON (q_ReportMonths_RosettaKeywords.AssignedMonth = q_InternalSummarybyDate_Rosetta.Date) AND (q_ReportMonths_RosettaKeywords.Keyword = q_InternalSummarybyDate_Rosetta.Keyword) AND (q_ReportMonths_RosettaKeywords.Page = q_InternalSummarybyDate_Rosetta.Page)) LEFT JOIN q_ExternalSummarybyMonth_Rosetta ON (q_ReportMonths_RosettaKeywords.AssignedMonth = q_ExternalSummarybyMonth_Rosetta.Date) AND (q_ReportMonths_RosettaKeywords.Keyword = q_ExternalSummarybyMonth_Rosetta.Keyword) AND (q_ReportMonths_RosettaKeywords.Page = q_ExternalSummarybyMonth_Rosetta.Page)) LEFT JOIN q_Rosetta_Rankings_Monthly ON (q_ReportMonths_RosettaKeywords.AssignedMonth = q_Rosetta_Rankings_Monthly.DateMod) AND (q_ReportMonths_RosettaKeywords.Keyword = q_Rosetta_Rankings_Monthly.Keyword) AND (q_ReportMonths_RosettaKeywords.Page = q_Rosetta_Rankings_Monthly.Page);

The trouble I'm having is the following:

LEFT JOIN q_Rosetta_Rankings_Monthly ON (q_ReportMonths_RosettaKeywords.AssignedMonth = q_Rosetta_Rankings_Monthly.DateMod)

AssignedMonth is pulled from a table where the field has a Data Type of Date/Time.
DateMod is calculated as DateSerial(Year([DateField]),Month([DateField]),1)

When I join these two fields, I get the following error message: "Data type mismatch in criteria expression."

Any ideas?

 

[jcfraun]

Never mind. I figured it out. There was a bad record in one of the upstream queries.