?
Solved

Data type mismatch

Posted on 2011-03-25
12
Medium Priority
?
193 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:RishiSingh05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 75
ID: 35217483
You will probably need to post the SQL for your query :-)

mx
0
 

Author Comment

by:RishiSingh05
ID: 35217488
Yes !! I forgot  ...

SELECT "Members" AS Id, Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Left(Revenue.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.[Subscriber ID] = [6CareODSCohort].MedicaidID) AND (Revenue.SMARTMth = [6CareODSCohort].Month)
GROUP BY "Members", Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Left(Revenue.SMARTMth,4),Mid(Revenue.SMARTMth,5),1)), [Cohort] & " " & [Dual/Medicaid];
0
 
LVL 75
ID: 35217516
I'm not seeing a Criteria expression or SMARTQtr   ?? Is there a missing WHERE clause ?

mx
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:RishiSingh05
ID: 35217576
There is no missing WHERE clause.  This is the expession for SMARTQtry:

SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Left(Revenue.SMARTMth,4),Mid(Revenue.SMARTMth,5),1))
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35217632
Can't see anything obvious .
What is the datatype of smartmth?
0
 

Author Comment

by:RishiSingh05
ID: 35217657
text
0
 

Author Comment

by:RishiSingh05
ID: 35217702
This query works.  It does not have the expression:
SMARTQtr: Left(Revenue.SMARTMth,4) & " Q" & DatePart("q",DateSerial(Left(Revenue.SMARTMth,4),Mid(Revenue.SMARTMth,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].MedicaidID)
GROUP BY "Members", Revenue.SMARTMth, [Cohort] & " " & [Dual/Medicaid];
===
0
 

Author Comment

by:RishiSingh05
ID: 35217766
Now it works !!

SELECT "Members" AS Id, Left([Revenue].[SMARTMth],4) & " Q" & DatePart("q",DateSerial(Left([Revenue].[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].MedicaidID)
GROUP BY "Members", Left([Revenue].[SMARTMth],4) & " Q" & DatePart("q",DateSerial(Left([Revenue].[SMARTMth],4),Mid([Revenue].[SMARTMth],5),1)), [Cohort] & " " & [Dual/Medicaid];
0
 

Author Comment

by:RishiSingh05
ID: 35217793
I have two databases.  The above SQL works in one db but not in the other ...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35217899
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.
0
 

Author Comment

by:RishiSingh05
ID: 35217952
ok
0
 

Author Comment

by:RishiSingh05
ID: 35244065
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].MedicaidID)
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(Left([Revenue.SMARTMth],4),Mid([Revenue].[SMARTMth],5),1))

so that the query becomes:
===
SELECT "Members" AS Id, Left([Revenue.SMARTMth],4) & " Q" & DatePart("q",DateSerial(Left([Revenue.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].MedicaidID)
GROUP BY "Members", Left([Revenue.SMARTMth],4) & " Q" & DatePart("q",DateSerial(Left([Revenue.SMARTMth],4),Mid([Revenue].[SMARTMth],5),1)), [Cohort] & " " & [Dual/Medicaid];
===

I get an error message:  "Data type mismatch in criteria expression"
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question