Data Type Mismatch in Criteria Expression

[Hrithika]

I am not sure why I am getting this error message.

I did use the datevalue function to extract the date part from the field in an odbc linked table

Given below are two different version of the queries that tried. Could someone please point out what I am doing wrong.

SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=#1/27/2012#) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));




SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=DateValue('1/27/2012')) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));

 

[pbaldy]

Does it work without the HAVING clause (which would be more efficient as a WHERE clause)? If so, what are the data types of the 2 fields? If not, you've likely got a mismatch in the joined fields.

 

[pbaldy]

Looks to me like you need quotes:

AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));

Should be

AND ((CONCERNCASEROLE.TYPECODE)="'CLRP'"));

And the first example with the date is correct but just needs the quotes on this part.

Really? I have never needed to add extra quotes like that directly in a query. It does not appear this is being built in code. Just tested and this worked fine:

WHERE tblHRData.EmpFirstName="Paul"

 

[Hrithika]

Does it work without the HAVING clause (which would be more efficient as a WHERE clause)? If so, what are the data types of the 2 fields? If not, you've likely got a mismatch in the joined fields.

The query works perfectly if I remove the filter from the activity.starttime field. I have to use group by to eliminate multiple instance of the record. I guess I have to keep having clause with the group by.

 

[Hrithika]

Really? I have never needed to add extra quotes like that directly in a query. It does not appear this is being built in code. Just tested and this worked fine:

WHERE tblHRData.EmpFirstName="Paul"

I did what Bob suggested. It just allowed the query to run, without generating error message but did not fetch any records.

 

[Hrithika]

I tried changing to where instead of having. It still gives the same error message

 

[pbaldy]

What is the data type of that field? Can you post the db here?

 

[Hrithika]

The database is too big to attach. It has too many odbc linked tables. Attached is the screenshot showing the datatype.