RishiSingh05
asked on
Data type mismatch
When I run the following query I get an error: "Data type mismatch in criteria expression"
If I remove the SMARTQtr expression the query runs ok. What is wrong with the SMARTQtr expression? It seems to work in other queries. Thanks.
If I remove the SMARTQtr expression the query runs ok. What is wrong with the SMARTQtr expression? It seems to work in other queries. Thanks.
ASKER
Yes !! I forgot ...
SELECT "Members" AS Id, Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le ft(Revenue .SMARTMth, 4),Mid(Rev enue.SMART Mth,5),1)) AS SMARTQtr, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI D) AND (Revenue.SMARTMth = [6CareODSCohort].Month)
GROUP BY "Members", Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le ft(Revenue .SMARTMth, 4),Mid(Rev enue.SMART Mth,5),1)) , [Cohort] & " " & [Dual/Medicaid];
SELECT "Members" AS Id, Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI
GROUP BY "Members", Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le
I'm not seeing a Criteria expression or SMARTQtr ?? Is there a missing WHERE clause ?
mx
mx
ASKER
There is no missing WHERE clause. This is the expession for SMARTQtry:
SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le ft(Revenue .SMARTMth, 4),Mid(Rev enue.SMART Mth,5),1))
SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
text
ASKER
This query works. It does not have the expression:
SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le ft(Revenue .SMARTMth, 4),Mid(Rev enue.SMART Mth,5),1))
===
SELECT "Members" AS Id, Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI D)
GROUP BY "Members", Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid];
===
SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Le
===
SELECT "Members" AS Id, Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI
GROUP BY "Members", Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid];
===
ASKER
Now it works !!
SELECT "Members" AS Id, Left([Revenue].[SMARTMth], 4) & " Q" & DatePart("q",DateSerial(Le ft([Revenu e].[SMARTM th],4),Mid ([Revenue] .[SMARTMth ],5),1)) AS SMARTQtr, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI D)
GROUP BY "Members", Left([Revenue].[SMARTMth], 4) & " Q" & DatePart("q",DateSerial(Le ft([Revenu e].[SMARTM th],4),Mid ([Revenue] .[SMARTMth ],5),1)), [Cohort] & " " & [Dual/Medicaid];
SELECT "Members" AS Id, Left([Revenue].[SMARTMth],
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI
GROUP BY "Members", Left([Revenue].[SMARTMth],
ASKER
I have two databases. The above SQL works in one db but not in the other ...
Okay, then you need to compare the datatypes in the two databases for the [Revenue] table and any other tables involved in that bit of the WHERE clause.
ASKER
ok
ASKER
To summarize my problem:
The following query works ok:
===
SELECT "Members" AS Id, Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI D)
GROUP BY "Members", Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid];
===
But if I change SMARTMth to this expression: SMARTQtr: Left([Revenue.SMARTMth],4) & " Q" & DatePart("q",DateSerial(Le ft([Revenu e.SMARTMth ],4),Mid([ Revenue].[ SMARTMth], 5),1))
so that the query becomes:
===
SELECT "Members" AS Id, Left([Revenue.SMARTMth],4) & " Q" & DatePart("q",DateSerial(Le ft([Revenu e.SMARTMth ],4),Mid([ Revenue].[ SMARTMth], 5),1)) AS SMARTQtr, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI D)
GROUP BY "Members", Left([Revenue.SMARTMth],4) & " Q" & DatePart("q",DateSerial(Le ft([Revenu e.SMARTMth ],4),Mid([ Revenue].[ SMARTMth], 5),1)), [Cohort] & " " & [Dual/Medicaid];
===
I get an error message: "Data type mismatch in criteria expression"
The following query works ok:
===
SELECT "Members" AS Id, Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid] AS Cohort2, Sum(Revenue.[Member Count]) AS [SumOfMember Count]
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI
GROUP BY "Members", Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid];
===
But if I change SMARTMth to this expression: SMARTQtr: Left([Revenue.SMARTMth],4)
so that the query becomes:
===
SELECT "Members" AS Id, Left([Revenue.SMARTMth],4)
FROM Revenue LEFT JOIN 6CareODSCohort ON (Revenue.SMARTMth = [6CareODSCohort].Month) AND (Revenue.[Subscriber ID] = [6CareODSCohort].MedicaidI
GROUP BY "Members", Left([Revenue.SMARTMth],4)
===
I get an error message: "Data type mismatch in criteria expression"
mx